Choosing an Identity Field for each Extent
[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.]
In Microsoft code name “M”, an identity is a special constraint that is placed on a single field or a collection of fields in an extent. The values stored in the identity fields must be able to uniquely identify the records stored in the extent. Note that it is impossible to have relationships between extents without first defining the identity.
Defining an Identity
To define the identity for an extent, use the where identity clause as in the following example.
PhoneNumbers :
{(
{
Id : Integer64;
Work : Text;
Home : Text?;
Mobile : Text?;
}
)*} where identity Id;
In this example, the PhoneNumbers extent contains an Id field of type Integer64. The where identity clause at the end of the extent definition specifies that this is the identity for the extent. If this extent were loaded into a database, the Id field would be the primary key of the PhoneNumbers table.
The value of an identity field can also be automatically assigned when new records are inserted into the extent. To automatically assign this value, set the default value for the identity field to be AutoNumber. When this technique is used, you do not explicitly set the value of the identifier for new records. The following example shows this modification to the PhoneNumbers extent.
PhoneNumbers :
{(
{
Id : Integer64 => AutoNumber;
Work : Text;
Home : Text?;
Mobile : Text?;
}
)*} where identity Id;
It is also possible to define the identity for the extent in a separate type. The following example specifies the identity in the HasId type and then the PhoneNumbers extent references that type.
type HasId
{
Id : Integer64 => AutoNumber;
} where identity Id;
PhoneNumbers :
{(
HasId &
{
Work : Text;
Home : Text?;
Mobile : Text?;
}
)*};
Note
Note that when you are using SQL Server Modeling Services, you can use the system-provided type, HasAutoId, instead of defining your own type as is done in this example.
For more technical details about identities in “M”, see Identity ("M" Programming Guide).