Skip to Main Content
SQL in a Nutshell, 3rd Edition
book

SQL in a Nutshell, 3rd Edition

by Kevin Kline
November 2008
Intermediate to advanced content levelIntermediate to advanced
591 pages
17h 28m
English
O'Reilly Media, Inc.
Content preview from SQL in a Nutshell, 3rd Edition

FOREIGN KEY Constraints

A FOREIGN KEY constraint defines one or more columns in a table as referencing columns in 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 when there is no matching value in the related table. They are the primary means of identifying the relationships between tables in a relational database. Here are 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 it’s 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Effective SQL: 61 Specific Ways to Write Better SQL, First Edition

Effective SQL: 61 Specific Ways to Write Better SQL, First Edition

John L. Viescas, Douglas J. Steele, Ben G. Clothier
SQL in a Nutshell, 4th Edition

SQL in a Nutshell, 4th Edition

Kevin Kline, Regina O. Obe, Leo S. Hsu

Publisher Resources

ISBN: 9780596155322Errata Page