Compartir a través de

Identity Columns (M to SQL Mapping)

  • Artículo

[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.]

Microsoft code name “M” modeling language entities can have one or more identity fields specified that make up a unique key for the entity. This identity is the basis for entity relationships (one-to-one, one-to-many and many-to-many relationships).

“M”->SQL supports single- or multiple-field identities, and creates a primary key that contains those columns. “M”->SQL supports any type for identity (except unconstrained Text). It also supports the identity auto-numbering scheme of SQL.

“M”->SQL explicitly specifies the clustered keyword for the primary key even though it is the default for SQL. The clustered index is the index in which the data for the table is actually stored. This allows you to look it up very quickly when you use the primary key to look it up.

Unconstrained Text field identities are not supported because SQL cannot create a primary key for them. To use a Text field as an identity, constrain its length as shown in the following example.

Name : Text#50

Collection fields and fields that can be null are presently unsupported as part of a primary key.

Primary keys that take more than 900 bytes to store are presently unsupported due to limitations in SQL and print an error.

Examples

“M” Example SQL Example

Simple Identity

 
module M {
    type Person {
        PersonId : Integer32;
        Name : Text;
    } where identity PersonId;
    People : Person*;
}
 
create table [M].[People]
(
  [PersonId] int not null,
  [Name] nvarchar(max) not null,
  constraint [PK_People] primary key clustered ([PersonId])
);

Auto-numbering Identity

 
module M {
    type Person {
        PersonId : Integer32 => AutoNumber();
        Name : Text;
    } where identity PersonId;
    People : Person*;
}
 
create table [M].[People]
(
  [PersonId] int not null identity,
  [Name] nvarchar(max) not null,
  constraint [PK_People] primary key clustered ([PersonId])
);

Guid Identity

 
module M {
    type Person {
        PersonId : Guid => NewGuid();
        Name : Text;
    } where identity PersonId;
    People : Person*;
}
 
create table [M].[People]
(
  [PersonId] uniqueidentifier not null default newid(),
  [Name] nvarchar(max) not null,
  constraint [PK_People] primary key clustered ([PersonId])
);

Multiple Field Identity

 
module M {
    type Person {
        FirstName : Text#100;
        LastName : Text#100;
    } where identity(FirstName,LastName);
    People : Person*;
}
 
create table [M].[People]
(
  [FirstName] nvarchar(100) not null,
  [LastName] nvarchar(100) not null,
  constraint [PK_People] primary key clustered ([FirstName], [LastName])
);