Compartir a través de

Constraints (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.]

Aside from membership and identity constraints, all where constraints on a type are translated in Microsoft code name “M”->SQL as check constraints. To allow a richer set of expressions, check constraints are always placed in a function that returns a Logical value.

Before check constraints are created, identity and foreign key constraints are removed from the expression (that is, they are not turned into check constraints). It does this by breaking up the expression by &&’s. So if “M” has a constraint like: where identity(Id) && Address in Addresses && Age > 10, Age > 10 is turned into a check constraint, but the first two are not because they are identity and foreign key constraints, respectively.

Examples

“M” Example SQL Example

Column constraint

 
module M {
    type Person {
        Gender : Text where value == "M" || value == "F";
    };
    People : Person*;
}
 
create function [M].[People_check_1]
(
  @Gender as nvarchar(max)
)
returns bit  as
  begin
    return case
  when @Gender = N'M' or @Gender = N'F' then 1
  else 0
end
 
  end
go
 
create table [M].[People]
(
  [Gender] nvarchar(max) not null,
  check ([M].[People_check_1]([Gender]) = 1)
);

Extent constraint

 
module M {
    type Person {
        Gender : Text;
    } where value.Gender == "M" || value.Gender == "F";
    People : Person*;
}
 
create function [M].[People_check_1]
(
  @Gender as nvarchar(max)
)
returns bit  as
  begin
    return case
  when @Gender = N'M' or @Gender = N'F' then 1
  else 0
end
 
  end
go
 
create table [M].[People]
(
  [Gender] nvarchar(max) not null,
  check ([M].[People_check_1]([Gender]) = 1)
);

Unique constraint

 
module M {
    type Person {
        FirstName : Text#100;
        LastName : Text#100;
        Gender : Text;
    } where unique (FirstName, LastName);
    People : Person*;
}
 
create table [M].[People]
(
  [FirstName] nvarchar(100) not null,
  [Gender] nvarchar(max) not null,
  [LastName] nvarchar(100) not null,
  constraint [Unique_People_FirstName_LastName] unique ([FirstName], [LastName])
);

Identity constraint

 
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])
);

Foreign key constraint

 
module M {
    type Person {
        PersonId : Integer32
            => AutoNumber();
        Name : Text;
    } where identity PersonId;
    People : Person*;
    
    type CurrentSession {
        Person : People;
        StartTime : Time;
    };
    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 [FK_CurrentSessions_Person_People] foreign key ([Person]) references [M].[People] ([PersonId])
);