[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”->SQL supports one-to-many (and one to one) relationships using collection fields. Unlike other entity fields, collection fields do not translate directly to a column in the underlying SQL table. Instead, a many-to-many "child table" is created with the name "<Extent>.<Field>", with a Parent column that refers to the containing row and a "Value" column to hold the values. If the parent has a multi-column primary key, the Parent reference in the child table is multi-column as well. This follows the same naming conventions as entity references.
For collections of entities, the “Value” column(s) of the child table is a foreign key to the storage in the same manner as a one-to-many relationship.
The child table has “on delete cascade” specified, so that if you delete a row in the parent table, corresponding rows in the child table are also deleted.
Examples
“M” Example
SQL Example
Many-to-many scalars
module M {
type Person {
Id : Integer32;
Name : Text;
EmailAddresses : Text*;
} where identity(Id);
People : Person*;
}
create table [M].[People]
(
[Id] int not null,
[Name] nvarchar(max) not null,
constraint [PK_People] primary key clustered ([Id])
);
create table [M].[People.EmailAddresses]([Id] bigint not null identity,[Parent] int not null,[Value] nvarchar(max) not null,constraint [PK_People.EmailAddresses] primary key clustered ([Id]),constraint [FK_People.EmailAddresses_Parent_M_People] foreign key ([Parent]) references [M].[People] ([Id]) on delete cascade);
Many-to-many entities
module M {
type Person {
Id : Integer32;
Name : Text;
Addresses : Addresses*;
} where identity(Id);
People : Person*;
type Address {
AddressId : Integer32;
Street : Text;
ZipCode : Integer32;
} where identity AddressId;
Addresses : Address*;
}
create table [M].[People]
(
[Id] int not null,
[Name] nvarchar(max) not null,
constraint [PK_People] primary key clustered ([Id])
);
go
create table [M].[Addresses]
(
[AddressId] int not null,
[Street] nvarchar(max) not null,
[ZipCode] int not null,
constraint [PK_Addresses] primary key clustered ([AddressId])
);
create table [M].[People.Addresses]([Id] bigint not null identity,[Parent] int not null,[Value] int not null,constraint [PK_People.Addresses] primary key clustered ([Id]),constraint [FK_People.Addresses_Parent_M_People] foreign key ([Parent]) references [M].[People] ([Id]) on delete cascade,constraint [FK_People.Addresses_Value_M_Addresses] foreign key ([Value]) references [M].[Addresses] ([AddressId])
);
Many-to-many entities that refer to a table with multiple identity fields.
module M {
type Person {
FirstName : Text#100;
LastName : Text#100;
Friends : People*;
} 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])
);
create table [M].[People.Friends]([Id] bigint not null identity,[Parent_FirstName] nvarchar(100) not null,[Parent_LastName] nvarchar(100) not null,[Value_FirstName] nvarchar(100) not null,[Value_LastName] nvarchar(100) not null,constraint [PK_People.Friends] primary key clustered ([Id]),constraint [FK_People.Friends_Parent_FirstName_Parent_LastName_M_People] foreign key ([Parent_FirstName], [Parent_LastName]) references [M].[People] ([FirstName], [LastName]) on delete cascade,constraint [FK_People.Friends_Value_FirstName_Value_LastName_M_People] foreign key ([Value_FirstName], [Value_LastName]) references [M].[People] ([FirstName], [LastName]));
Many-to-many scalars where a parent table has multiple identity fields.
module M {
type Person {
FirstName : Text#100;
LastName : Text#100;
EmailAddresses : Text*;
} 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])
);
create table [M].[People.EmailAddresses]([Id] bigint not null identity,[Parent_FirstName] nvarchar(100) not null,[Parent_LastName] nvarchar(100) not null,[Value] nvarchar(max) not null,constraint [PK_People.EmailAddresses] primary key clustered ([Id]),constraint [FK_People.EmailAddresses_Parent_FirstName_Parent_LastName_M_People] foreign key ([Parent_FirstName], [Parent_LastName]) references [M].[People] ([FirstName], [LastName]) on delete cascade);
Collection fields are only supported if the containing entity has an identity field (so that you can map to the parent ID).