CREATE TABLE - References

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column, ..., index type[(width)] [ASC|DESC]
[CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...)
REFERENCES table [(column,...)]
   [MATCH FULL|MATCH PARTIAL|MATCH SIMPLE]
   [ON DELETE [RESTRICT|CASCADE|SET NULL|NO ACTION]]
   [ON UPDATE [RESTRICT|CASCADE|SET NULL|NO ACTION]]
[,...]) [options]

Explanation

This documentation page describes the REFERENCES options to the FOREIGN KEY clause, which creates a relationship between an index and another table. This information also applies to the REFERENCES column flag (see the CREATE TABLE - Column flags page).

The MATCH FULL clause requires that the reference match on the full width of each column indexed. In contrast, MATCH PARTIAL allows the use of partial columns. Partial columns can accelerate indexing when the first few characters of a column determine that a row is unique.

The ON DELETE clause instructs MySQL to react to deletions of matching rows from the foreign table according to the option that follows. The ON UPDATE clause causes MySQL to respond to updates made to the referenced table according to the options that follow it. You can use both clauses in the same CREATE TABLE statement.

The RESTRICT keyword option instructs MySQL not to allow the deletion or update (depending on the clause in which it's used) of the rows in the foreign table if rows in the current table are linked to them. The CASCADE keyword says that when deleting or updating the rows that are referenced in the parent table, delete or update the related rows in the child table accordingly.

SET NULL causes MySQL to change the data contained in the related columns to a NULL value. For this to work, the column in the child table must allow NULL values. The NO ACTION setting has MySQL not to react to deletions or updates with regard to the referencing table.