Presents your 
SQL SERVER E-NEWSLETTER for April 22, 2003

<------------------------------------------->

USE CREATE SCHEMA TO CREATE MUTUALLY DEPENDENT FOREIGN KEY CONSTRAINTS

CREATE SCHEMA is a great way to create mutually dependent foreign key
constraints without the multiple steps required to create each 
constraint individually.

CREATE SCHEMA is basically a statement used to create tables, views, and
permissions at one time. Like most statements, if any of the objects in
the CREATE SCHEMA statement fails, all object creation fails. The 
syntax of the statement is as follows:

CREATE SCHEMA AUTHORIZATION owner
     [<schema_element> [. . .n] ]
     <schema_element> ::=
          {table_definition | view_definition | grant_statement}

Objects can be created in any order--even if the object references
another object that has not yet been created. The only exception to 
this is views, which can only reference previously created views.

Another consideration is the AUTHORIZATION owner, which must be a valid
user. CREATE SCHEMA permissions default to all users; however, users
cannot use the CREATE SCHEMA statement to create an object unless they
already have permission to do so.

In other words, if users do not have permission, or have been denied
permission to create a table, CREATE SCHEMA will not let them create 
tables either.

The following example allows user Ross to create two tables (t1 and t2),
and creates mutual primary key constraints on each table's first 
column.

CREATE SCHEMA AUTHORIZATION ross
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT REFERENCE t2 (c1))
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT REFERENCE t1 (c1))

----------------------------------------