SQL in a Nutshell, 2nd Edition

FOREIGN KEY Constraints

A FOREIGN KEY constraint defines one or more columns in the table as referencing columns to a UNIQUE or PRIMARY KEY in another table. (A foreign key can reference a unique or primary key in the same table as the foreign key itself, but such foreign keys are rare.) Foreign keys can then prevent the entry of data into a table where there is no matching value in the related table. They are the primary means of identifying the relationship between tables in a relational database. Some rules about foreign keys:

• Many foreign keys may exist on a table at a time.

• A foreign key can be declared to reference either the primary key or a unique key of another table to establish a direct relationship between the two tables.

The full SQL2003 syntax for foreign keys is more elaborate than the general syntax for constraints shown earlier, and is dependent on whether you are making a table-level or column-level declaration:

-- Table-level foreign key
[CONSTRAINT [constraint_name] ] 
FOREIGN KEY (local_column [,...] )
REFERENCES referenced_table [ (referenced_column [,...]) ]
[MATCH {FULL | PARTIAL | SIMPLE} ]
[ON UPDATE {NO ACTION | CASCADE | RESTRICT | 
   SET NULL | SET DEFAULT} ]
[ON DELETE {NO ACTION | CASCADE | RESTRICT | 
   SET NULL | SET DEFAULT} ]
[constraint_deferment] [deferment_timing]

-- Column-level foreign key
[CONSTRAINT [constraint_name] ] 
REFERENCES referenced_table [ (referenced_column [,...]) ] [MATCH {FULL | PARTIAL | SIMPLE} ] [ON UPDATE {NO ACTION | CASCADE | RESTRICT ...