Compartir a través de

Entity References: One-To-One and One-To-Many (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.]

One-to-one and one-to-many relationships in Microsoft code name “M” are modeled using entity references. To implement the one-to-many pattern, you place an entity reference to the Parent in the child extent (OtherTable : OtherType). To implement one-to-one, you place an entity reference from one of the extents to the other (OtherTable : OtherType). Both look the same in “M”. To enforce the 1:1 relationship, a unique constraint can be used.

“M”->SQL implements entity references with a column with the same type as an entity's identity column and a foreign key from the referencing table to the referenced table. Trees are self-referencing one-to-many relationships.

Examples

“M” Example SQL Example

One-to-many

 
module M {
    type Person {
        Id : Integer32
            => AutoNumber();
        Name : Text;
    } where identity Id;
    People : Person*;
    
    type TaxReturn {
        Id : Integer32;
        Year : Integer16;
        Citizen : People;
    } where identity Id;
    TaxReturns : TaxReturn*;
}
 
create table [M].[People]
(
  [Id] int not null identity,
  [Name] nvarchar(max) not null,
  constraint [PK_People] primary key clustered ([Id])
);
go
create table [M].[TaxReturns]
(
  [Id] int not null,
  [Citizen] int not null,
  [Year] smallint not null,
  constraint [PK_TaxReturns] primary key clustered ([Id]),
  constraint [FK_TaxReturns_Citizen_M_People] foreign key ([Citizen]) references [M].[People] ([Id])
);

Tree

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

One-to-one

 
module M {
    type Person {
        PersonId : Integer32
            => AutoNumber();
        Name : Text;
    } where identity PersonId;
    People : Person*;
    
    type CurrentSession {
        Person : People;
        StartTime : Time;
    } where unique Person;
    CurrentSessions : CurrentSession*;
}
 
create table [M].[People]
(
  [PersonId] int not null identity,
  [Name] nvarchar(max) not null,
  constraint [PK_People] primary key clustered ([PersonId])
);
 
create table [M].[CurrentSessions]
(
  [Person] int not null,
  [StartTime] time not null,
  constraint [Unique_CurrentSessions_Person] unique ([Person]),constraint [FK_CurrentSessions_Person_People] foreign key ([Person]) references [M].[People] ([PersonId])
);

References to entities with multiple identity fields.

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

“M”->SQL generates an error if you have an entity reference without a membership constraint. For example, Friend : Person generates an error, while Friend : People or Friend : Person where value in People do not.