O'Reilly logo

SQL in a Nutshell, 3rd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required