Database Generation Rules (Generate Database Wizard)

  • Article

The Create Database Wizard generates a database schema from a conceptual model by using a table-per-type mapping strategy. A table-per-type mapping strategy is a mapping strategy in which all non-inherited properties on each entity type are mapped to separate database tables. This topic describes the rules that are used to generate the database schema.

For more information, see How to: Generate a Database from a Conceptual Model (Entity Data Model Tools) and How to: Customize Database Generation (Generate Database Wizard).

Entity Types and Tables

The following table describes how tables are created based on entity types:

Table name

Created columns1

Primary key

Foreign keys

Non-derived type

The type's EntitySet element name.

A column for each scalar property, including each scalar property of complex type properties.

Column or columns that correspond to the entity key property or properties.

See the "Associations and Foreign Keys" section, below.

Derived type

Concatenation of the base type's EntitySet element name and the type name.

A column for each non-inherited scalar property (including each scalar property of complex type properties) and a column for each inherited key property.

Column or columns that correspond to inherited entity key property or properties.

The primary key of the child table is also a foreign key that references the primary key of its parent table.

Additional foreign keys may be created. For more information, see the "Associations and Foreign Keys" section, below.
  1. Additional columns can be added for navigation properties. For more information, see the "Associations and Foreign Keys" section, later in this topic.

Example

The following example shows the data description language (DDL) that is generated for a model with the following description:

  • Person entity type (entity set: PersonSet):

    • PersonID (key property, Int32)

    • LastName (String)

    • FirstName (String)

  • Instructor entity type (inherits from Person, inherited properties are not listed):

    • DepartmentID (Int32)

    • OfficeLocation (String)

Code

-- --------------------------------------------------
-- Creating All Tables
-- --------------------------------------------------

-- Creating table 'PersonSet'
CREATE TABLE [PersonSet] (
      [PersonID] int  NOT NULL,
      [LastName] nvarchar(max)  NOT NULL,
      [FirstName] nvarchar(max)  NOT NULL
);
GO
-- Creating table 'PersonSet_Instructor'
CREATE TABLE [PersonSet_Instructor] (
      [DepartmentID] int  NOT NULL,
      [OfficeLocation] nvarchar(max)  NOT NULL,
      [PersonID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating All Primary Key Constraints
-- --------------------------------------------------

-- Creating primary key on [PersonID] in table 'PersonSet'
ALTER TABLE [PersonSet] WITH NOCHECK
ADD CONSTRAINT [PK_PersonSet]
      PRIMARY KEY CLUSTERED ([PersonID] ASC)
    ON [PRIMARY]
GO
-- Creating primary key on [PersonID] in table 'PersonSet_Instructor'
ALTER TABLE [PersonSet_Instructor] WITH NOCHECK
ADD CONSTRAINT [PK_PersonSet_Instructor]
      PRIMARY KEY CLUSTERED ([PersonID] ASC)
    ON [PRIMARY]
GO

-- --------------------------------------------------
-- Creating All Foreign Key Constraints
-- --------------------------------------------------

-- Creating foreign key on [PersonID] in table 'PersonSet_Instructor'
ALTER TABLE [PersonSet_Instructor] WITH NOCHECK
ADD CONSTRAINT [FK_Instructor_inherits_Person]
    FOREIGN KEY ([PersonID])
    REFERENCES [PersonSet]
        ([PersonID])
    ON DELETE NO ACTION ON UPDATE NO ACTION
GO

Associations and Foreign Keys

The table below describes the DDL generation rules for associations. Note that for all associations, a foreign key constraint is created.

Association type Foreign key generation rule

One-to-zero-or-ne (1:0..1)

- or -

One-to-many (1:*)

If no referential constraint is defined on the association, columns are added to the table that corresponds to the entity type on the 0..1 or * end of the association. The added columns have foreign key constraints that reference the primary key of the table that corresponds to the entity type on the other end of the association. The added table columns are mapped to the association, not the entity type. The name of each added column is the concatenation of the navigation property name and the key property name.

If a referential constraint exists on the association in the conceptual model, no additional columns are added to the database table. Instead, a foreign key constraint is created. The constraint that is created in the database mirrors the constraint in the conceptual model: columns that correspond to the dependent properties in the conceptual model reference the primary key columns that correspond to the entity key. The name of the foreign key constraint is the name of the association. For more information, see How to: Add and Edit Referential Constraints (Entity Data Model Tools).

One-to-one (1:1)

If no referential constraint is defined on the association, columns are added to one of the tables that correspond to entity types on the ends of the association. The table to which the columns are added is chosen arbitrarily. The added columns have foreign key constraints that reference the primary key of the table that corresponds to the entity type on the other end of the association. The added columns are mapped to the association, not the entity type. The name of each added column is the concatenation of the navigation property name and the key property name.

If a referential constraint exists on the association in the conceptual model, no additional columns are added to the database table. Instead, a foreign key constraint is created. The constraint that is created in the database mirrors the constraint in the conceptual model: columns that correspond to the dependent properties in the conceptual model reference the primary key columns that correspond to the entity key. The name of the foreign key constraint is the name of the association. For more information, see How to: Add and Edit Referential Constraints (Entity Data Model Tools).

Many-to-many (*:*)

A join table is created. For each key property in each entity type, a column is added to the table. The columns have foreign key constraints that reference the primary keys in the tables created based on the entity types on the ends of the association. The primary key of the created table will be a compound primary key that consists of all the columns in the table.

The name of the created table is the same as the AssociationSet element name.

Example

The following example shows the DDL that is generated for a 1:* association. In this model, there is a 1:* association between the Subject entity and the Course entity:

  • Subject entity type (entity set: SubjectSet):

    • SubjectID (key property, Int32)

    • DepartmentID (key property, Int32)

    • SubjectName (String)

  • Course entity type (entity set: CourseSet):

    • CourseID (key property, Int32)

    • SectionID (key property, Int32)

    • CourseName (String)

Code

-- --------------------------------------------------
-- Creating All Tables
-- --------------------------------------------------

-- Creating table 'SubjectSet'
CREATE TABLE [SubjectSet] (
      [SubjectID] int  NOT NULL,
      [DepartmentID] int  NOT NULL,
      [SubjectName] nvarchar(max)  NOT NULL
);
GO
-- Creating table 'CourseSet'
CREATE TABLE [CourseSet] (
      [CourseID] int  NOT NULL,
      [SectionID] int  NOT NULL,
      [CourseName] nvarchar(max)  NOT NULL,
      [Subject_SubjectID] int  NOT NULL,
      [Subject_DepartmentID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating All Primary Key Constraints
-- --------------------------------------------------

-- Creating primary key on [SubjectID], [DepartmentID] in 
-- table 'SubjectSet'
ALTER TABLE [SubjectSet] WITH NOCHECK
ADD CONSTRAINT [PK_SubjectSet]
      PRIMARY KEY CLUSTERED ([SubjectID], [DepartmentID] ASC)
    ON [PRIMARY]
GO
-- Creating primary key on [CourseID], [SectionID] in table 'CourseSet'
ALTER TABLE [CourseSet] WITH NOCHECK
ADD CONSTRAINT [PK_CourseSet]
      PRIMARY KEY CLUSTERED ([CourseID], [SectionID] ASC)
    ON [PRIMARY]
GO

-- --------------------------------------------------
-- Creating All Foreign Key Constraints
-- --------------------------------------------------

-- Creating foreign key on [Subject_SubjectID], [Subject_DepartmentID]
-- in table 'CourseSet'
ALTER TABLE [CourseSet] WITH NOCHECK
ADD CONSTRAINT [SubjectCourse]
    FOREIGN KEY ([Subject_SubjectID], [Subject_DepartmentID])
    REFERENCES [SubjectSet]
        ([SubjectID], [DepartmentID])
    ON DELETE NO ACTION ON UPDATE NO ACTION
GO

Example

The following example shows the DDL that is generated for a *:* association. In this model, there is a *:* association between the Student entity and the Course entity:

  • Student entity type (entity set: StudentSet):

    • StudentID (key property, Int32)

    • LastName (String)

    • FirstName (String)

  • Course entity type (entity set: CourseSet):

    • CourseID (key property, Int32)

    • SectionID (key property, Int32)

    • CourseName (String)

Code

-- --------------------------------------------------
-- Creating All Tables
-- --------------------------------------------------

-- Creating table 'StudentSet'
CREATE TABLE [StudentSet] (
      [StudentID] int  NOT NULL,
      [LastName] nvarchar(max)  NOT NULL,
      [FirstName] nvarchar(max)  NOT NULL
);
GO
-- Creating table 'CourseSet'
CREATE TABLE [CourseSet] (
      [CourseID] int  NOT NULL,
      [SectionID] int  NOT NULL,
      [CourseName] nvarchar(max)  NOT NULL
);
GO
-- Creating table 'StudentCourse'
CREATE TABLE [StudentCourse] (
      [Student_StudentID] int  NOT NULL,
      [Course_CourseID] int  NOT NULL,
      [Course_SectionID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating All Primary Key Constraints
-- --------------------------------------------------

-- Creating primary key on [StudentID] in table 'StudentSet'
ALTER TABLE [StudentSet] WITH NOCHECK
ADD CONSTRAINT [PK_StudentSet]
      PRIMARY KEY CLUSTERED ([StudentID] ASC)
    ON [PRIMARY]
GO
-- Creating primary key on [CourseID], [SectionID] in table 'CourseSet'
ALTER TABLE [CourseSet] WITH NOCHECK
ADD CONSTRAINT [PK_CourseSet]
      PRIMARY KEY CLUSTERED ([CourseID], [SectionID] ASC)
    ON [PRIMARY]
GO
-- Creating primary key on [Student_StudentID], [Course_CourseID],
-- [Course_SectionID] in table 'StudentCourse'
ALTER TABLE [StudentCourse] WITH NOCHECK
ADD CONSTRAINT [PK_StudentCourse]
      PRIMARY KEY CLUSTERED ([Student_StudentID], [Course_CourseID], [Course_SectionID] ASC)
    ON [PRIMARY]
GO

-- --------------------------------------------------
-- Creating All Foreign Key Constraints
-- --------------------------------------------------

-- Creating foreign key on [Student_StudentID] in table 'StudentCourse'
ALTER TABLE [StudentCourse] WITH NOCHECK
ADD CONSTRAINT [FK_StudentCourse_StudentSet]
    FOREIGN KEY ([Student_StudentID])
    REFERENCES [StudentSet]
        ([StudentID])
    ON DELETE NO ACTION ON UPDATE NO ACTION
GO
-- Creating foreign key on [Course_CourseID], [Course_SectionID] in table 'StudentCourse'
ALTER TABLE [StudentCourse] WITH NOCHECK
ADD CONSTRAINT [FK_StudentCourse_CourseSet]
    FOREIGN KEY ([Course_CourseID], [Course_SectionID])
    REFERENCES [CourseSet]
        ([CourseID], [SectionID])
    ON DELETE NO ACTION ON UPDATE NO ACTION
GO

Example

The following example shows the DDL that is generated for a 1:0..1 association when a referential constraint exists on the association. In this model, InstructorId is the principal key of the referential constraint and OfficeId is the dependent key:

  • Instructor entity type (entity set: InstructorSet):

    • InstructorId (key property, Int32)

    • LastName (String)

    • FirstName (String)

  • Office entity type (entity set: OfficeSet):

    • OfficeID (key property, Int32)

    • Location (key property, String)

Code

-- --------------------------------------------------
-- Creating All Tables
-- --------------------------------------------------

-- Creating table 'InstructorSet'
CREATE TABLE [InstructorSet] (
      [InstructorID] int  NOT NULL,
      [LastName] nvarchar(max)  NOT NULL,
      [FirstName] nvarchar(max)  NOT NULL
);
GO
-- Creating table 'OfficeSet'
CREATE TABLE [OfficeSet] (
      [OfficeID] int  NOT NULL,
      [Location] nvarchar(max)  NOT NULL,
      [Instructor_InstructorID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating All Primary Key Constraints
-- --------------------------------------------------

-- Creating primary key on [InstructorID] in table 'InstructorSet'
ALTER TABLE [InstructorSet] WITH NOCHECK
ADD CONSTRAINT [PK_InstructorSet]
     PRIMARY KEY CLUSTERED ([InstructorID] ASC)
    ON [PRIMARY]
GO
-- Creating primary key on [OfficeID], [Location] in table 'OfficeSet'
ALTER TABLE [OfficeSet] WITH NOCHECK
ADD CONSTRAINT [PK_OfficeSet]
      PRIMARY KEY CLUSTERED ([OfficeID], [Location] ASC)
    ON [PRIMARY]
GO

-- --------------------------------------------------
-- Creating All Foreign Key Constraints
-- --------------------------------------------------

-- Creating foreign key on [Instructor_InstructorID] in 
-- table 'OfficeSet'
ALTER TABLE [OfficeSet] WITH NOCHECK
ADD CONSTRAINT [InstructorOffice]
    FOREIGN KEY ([Instructor_InstructorID])
    REFERENCES [InstructorSet]
        ([InstructorID])
    ON DELETE NO ACTION ON UPDATE NO ACTION
GO

Complex Types and Columns

If an entity type has a ComplexType property, a column for each scalar property of the ComplexType is added to the table that is created based on the entity type. The name of each column is the concatenation of the ComplexType property name, an underscore character ("_"), and the name of the corresponding property of the ComplexType. For example, suppose an entity has a ComplexType property with the name EntityProperty1, and the ComplexType has two scalar properties, CTProperty1 and CTProperty2. The columns added to the table are EntityProperty1_CTProperty1 and EntityProperty1_CTProperty2.

Example

The following example shows the DDL that is generated for an entity with a ComplexType property:

  • Instructor entity type (entity set: InstructorSet):

    • InstructorID (key property, Int32)

    • LastName (String)

    • FirstName (String)

    • ContactInfo (of ComplexType ContactInformation)

  • ContactInformation ComplexType:

    • HomePhone (String)

    • CellPhone (String)

    • EmailAddress (String)

Code

-- --------------------------------------------------
-- Creating All Tables
-- --------------------------------------------------

-- Creating table 'InstructorSet'
CREATE TABLE [InstructorSet] (
      [InstructorID] int  NOT NULL,
      [LastName] nvarchar(max)  NOT NULL,
      [FirstName] nvarchar(max)  NOT NULL,
      [ContactInfo_HomePhone] nvarchar(max)  NOT NULL,
      [ContactInfo_CellPhone] nvarchar(max)  NOT NULL,
      [ContactInfo_EmailAddress] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating All Primary Key Constraints
-- --------------------------------------------------

-- Creating primary key on [InstructorID] in table 'InstructorSet'
ALTER TABLE [InstructorSet] WITH NOCHECK
ADD CONSTRAINT [PK_InstructorSet]
      PRIMARY KEY CLUSTERED ([InstructorID] ASC)
    ON [PRIMARY]
GO

Facets and Data Types

Facets represent data type constraints on entity properties. The Generate Database Wizard does the following with facets that are specified in the conceptual schema definition language (CSDL):

  • Copies facets from Property elements in the CSDL to the corresponding Property elements in the store schema definition language (SSDL).

  • Translates facets into statements or constraints in the DDL:

    • The MaxLength, FixedLength, Precision, Scale, and Unicode facets are translated into data type modifiers on columns.

    • The Collation facet translates into a COLLATE statement.

    • If the StoreGeneratedPattern facet is set to identity (StoreGeneratedPattern="identity") and the property type is int, bigint, smallint, tinyint, or decimal, the affected database column will be created with the IDENTITY(1,1) specification. For more information, see Facets (CSDL) and How to: Edit Facet Values (Entity Data Model Tools).

Indexes

Clustered indexes are created on the primary keys for each table. All indexes are in ascending order.

DROP Statements

If a storage model is already defined when you run the Create Database Wizard, the generated DDL will contain a DROP TABLE statement and DROP CONSTRAINT statement for each EntitySet and each AssociationSet (respectively) that are inferred from the storage model.

See Also

Concepts

ADO.NET Entity Data Model Designer

Other Resources

Generate Database Wizard (Entity Data Model Tools)
EntitySet Element (CSDL)
CSDL, SSDL, and MSL Specifications