foreign keys and referential integrity

foreign key

a column, or group of columns, that refer to a primary key. the data types of the foreign and primary keys must be the same, but the names may be different. Foreign keys do not obey the same rules as primary keys. Foreign key values may be repeated and may be NULL. Foreign keys obey a relational rule called referential integrity. Referential integrity requires foreign key values must either be NULL or match some value of the referenced primary key

foreign key constraint

A foreign key constraint is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords. When a foreign key constraint is specified, the database rejects insert, update, and delete statements that violate referential integrity.

referential integrity rule

a fully NULL foreign key is a simple or composite foreign key in which all columns are NULL. Referential integrity is a relational rule that requires foreign key values that are either fully NULL or match some primary key value. In a relational database, foreign keys must obey referential integrity at all times. Occasionally, data entry errors or incomplete data result in referential integrity violations. Violations must be corrected before data is stored in the database

referential integrity violations

Referential integrity can be violated in four ways:

1. A primary key is updated.

2. A foreign key is updated.

3. A row containing a primary key is deleted.

4. A row containing a foreign key is inserted.

   only these four operations can violate referential integrity. Primary key inserts and foreign key deletes never violate referential integrity

referential integrity actions

An insert, update, or delete that violates referential integrity can be corrected manually. However, manual corrections are time-consuming and error-prone. Instead, databases automatically correct referential integrity violations with any of four actions, specified as SQL constraints:

• RESTRICT rejects an insert, update, or delete that violates referential integrity.

• SET NULL sets invalid foreign keys to NULL.

• SET DEFAULT sets invalid foreign keys to the foreign key default value.

• CASCADE propagates primary key changes to foreign keys.

CASCADE behaves differently for primary key updates and deletes. If a primary key is deleted, rows containing matching foreign keys are deleted. If a primary key is updated, matching foreign keys are updated to the same value.

RESTRICT, SET NULL, and SET DEFAULT apply to primary key update and delete, and foreign key insert and update. CASCADE applies to primary key update and delete only.

On UPDATE and ON DELETE clauses

For foreign key inserts and updates, MySQL supports only RESTRICT. Foreign key inserts and updates that violate referential integrity are automatically rejected.

For primary key updates and deletes, MySQL supports all four actions. Actions are specified in the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint. ON UPDATE and ON DELETE are followed by either RESTRICT, SET NULL, SET DEFAULT, or CASCADE.

ON UPDATE and ON DELETE determine what happens to the foreign key when the referenced primary key is updated or deleted. When several foreign keys refer to the same primary key, different actions can be specified for each foreign key.

MySQL has several limitations on primary key updates and deletes:

• RESTRICT is applied when the ON UPDATE or ON DELETE clause is omitted.

• SET NULL cannot be used when a foreign key is not allowed NULL values.

• SET DEFAULT is not supported in some MySQL configurations.

ON UPDATE and ON DELETE are standard SQL. The clauses are supported by most relational databases, but details and limitations vary.