Foreign key constraints implement one-to-many relationships.

A foreign key is a column in a table where each row matches a primary key value in another table, the foreign table. (Actually, a foreign key can also reference any other column or set of columns in the foreign table for which a UNIQUE constraint has been set. But this nearly always means a primary key.)

The foreign table (the referenced table) is the “one” side of the relationship, and the table that defines the constraint is the “many” side.

REFERENCES: the Column Form of the Foreign Key Constraint

REFERENCES is the column version of the foreign key constraint. If refcolumn is not specified, then the default reference to reftable‘s primary key is used. If refcolumn is specified, it must be either the primary key (in which case it is usually left out), or a single column to which the UNIQUE constraint has been applied, in the table referenced by reftable.

The column version of the constraint should be used except in the case of composite foreign keys (foreign keys with more than one column).

These two tables demonstrate a simple one-to-many relationship using the column version of the foreign key constraint:

Attempting to insert or update the color_id column in the fruits table with a value that isn’t in the id column of the colors table will result in an error.

Unless business rules dictate otherwise, the NOT NULL constraint should also be set on the column with the REFERENCES constraint, since a null value will always satisfy it. For an example of when business ruls would dictate otherwise, a tasks table might reference an employees table, and unassigned tasks could have a null value in an employee_id column until they were assigned.

FOREIGN KEY REFERENCES: the Table Form of the Foreign Key Constraint

The table constraint version of the foreign key constraint is required when using a composite foreign key. As an example, suppose we have an accounts table. Each account entry has an account number and an account type. Accounts of different types can have the same account number, so to uniquely identify each entry, we use a composite primary key.

We can set up the accounts table like this:

Now, we also have a sub_accounts table, in one-to-many relationship with accounts (one account, many sub-accounts). We want a foreign key that references the primary key in accounts.

We can set up the sub_accounts table like this:

If we prefer, we can further shorten line 6 to this: FOREIGN KEY (acc_num, acc_type) REFERENCES accounts, because we will reference the two primary key columns in accounts by default if we do not provide any refcolumn values.

The ON DELETE and ON UPDATE Clauses

These two clauses give some flexibility to what happens when deleting a row that a foreign key references, or when updating a key in a foreign table. The most common values are NO ACTION and CASCADE. NO ACTION, the default, will raise an error in this situation.

For example, suppose we have these values in our fruits and colors tables:

fruits

id	name	color_id
1	orange	3
2	cherry	1
3	lemon	2

colors

id	color
1	red
2	yellow
3	orange

Then, either of these two SQL statements:

Will have this result:

However, if we set up the fruits table like this:

Then, the DELETE statement will delete the cherry row from fruits, and the UPDATE statement will change the color_id in the cherry row to 5.