Chapter 8: Creating Databases and Tables

Using create schema for cross-referencing constraints

You cannot create a table that references a table that does not yet exist. To create two or more tables that reference each other, use create schema.

A schema is a collection of objects owned by a particular user, and the permissions associated with those objects. If any of the statements within a create schema statement fail, the entire command is rolled back as a unit, and none of the commands take effect.

The create schema syntax is:

create schema authorization authorization name
create_object_statement
     [create_object_statement ...]
[permission_statement ...]

For example:

create schema authorization dbo
    create table list1
        (col_a char(10) primary key,
          col_b char(10) null
        references list2(col_A))
    create table list2
        (col_A char(10) primary key,
          col_B char(10) null
        references list1(col_a))

General rules for creating referential integrity constraints

View this book as PDF