Identity ("M" Programming Guide)

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

It is often necessary to retrieve a specific entity from a collection. For example, you may want to see the Supplier who sells a particular Product. One way to do this is to design the entity to contain an identity field. An identity field of an entity is unique within the entity collection and non-null. It is also possible to represent an identity by multiple fields that are unique when taken together.

Note that identity is only applicable to entities.

You specify a field (or fields) as an identity field by defining an identity constraint on the type, or on an extent of that type (but not both). There can be only one identity on a type or constraint. An identity on a derived type overrides that of any type it derives from.

The best practice is to create a new numeric field to use for the identity. There are special functions that guarantee identity uniqueness, such as AutoNumber(), and NewGuid(). In SQL, such a field is known as a surrogate key.

Often entities already contain fields that might appear to be good candidates for identity. For example, a Person entity might contain FirstName, LastName, and SocialSecurityNumber fields. It is not advisable to use such fields, for two reasons.

• The field(s) might not be unique. This is obviously the case with FirstName and LastName, but it is also true of SocialSecurityNumber, which might appear to be unique, but is not guaranteed to be so.

• Identity fields are often linked to by other entities. If an identity field changed, it requires updating all the links, which sometimes is difficult. It is preferable to use an identity field that never changes.

Identity Examples

These examples shows the use of two functions that create numbers that are guaranteed to be unique within a given extent.

AutoNumber() Identity

The following example uses the AutoNumber() function to generate a T-SQL bigint identity field.

module Northwind {
    type Employee
    {
        Id : Integer64 => AutoNumber();
    } where identity Id;
    Employees : Employee;
}

The preceding code generates the following T-SQL code when compiled.

create table [Northwind].[Employees]
(
  [Id] bigint not null identity,
  constraint [PK_Employees] primary key clustered ([Id])
);

GUID Identity

The following example uses the NewGuid() function to create a T-SQL uniqueidentifier field as the identity.

module Northwind 
{
    type Employee
    {
        Id : Guid => NewGuid();
    } where identity Id;
    Employees : Employee;
}

The preceding code generates the following T-SQL code when compiled.

create table [Northwind].[Employees]
(
  [Id] uniqueidentifier not null,
  constraint [PK_Employees] primary key clustered ([Id])
);
go

create trigger [Northwind].[Employees_insert_trigger]
  on [Northwind].[Employees]
  instead of insert
  as
  begin
    insert into [Northwind].[Employees] ([Id])
    select (coalesce([t0].[Id], newid()))
    from inserted as [t0];
  end
go

Restrictions

There are a number of restrictions that apply to use of identity.

It is an error to refer to an entity that does not have an identity. In the following code, the Employee entity has a field, HomeAddress, which is of type Address, and whose value must be in the Addresses extent. Because the Address type and Addresses extent do not contain an identity constraint, the code generates compiler errors.

module Northwind 
{
    type Address 
    {
        Id : Guid => NewGuid();
        Street : Text;
    } // where identity(Id);
    type Employee
    {
        Id : Guid => NewGuid();
        HomeAddress : Address where value in Addresses;
    } where identity(Id);
    Addresses : {Address*};
    Employees : {Employee*};
}

It is an error for an entity without an identity to have a field that is a collection of any type. The following code generates compiler errors.

module Northwind 
{
    type Employee
    {
         FamilyMembers : {Text*};
    } 
    Employees : Employee;
}