The Geek Diary

How to use foreign keys to attain referential integrity in MySQL

by

Referential Integrity

Referential integrity means that relationships between tables are consistent. MySQL enforces referential integrity by using foreign key constraints. When one table (the child table) has a foreign key to another table (the parent table), MySQL prevents you from adding a record to the child table if there is no corresponding record in the parent table. It also facilitates cascading updates and deletes to ensure that changes made to the child table are reflected in the parent table.

Relationships determine the results of SQL statements:

  • An INSERT into a child table (without a foreign key to the parent table) does not complete.
  • Using foreign keys on joined columns improves performance.
  • When you UPDATE or DELETE data, related data in the linked table can be automatically updated or deleted with the CASCADE option.

Using Foreign Keys

Specify a key for every table in the relationship by using the most frequently queried column or columns, or an auto-increment value if there is no obvious primary key. A primary key is not mandatory, but there must be an index in the parent table which lists the referenced columns first, in the same order. For fast join performance, define foreign keys on the join columns, and declare those columns with the same data type in each table.

Referential Actions

If you attempt to insert or update data in a child column, and that value does not exist in the parent column, the operation fails. If you attempt to change a value in the parent column such that a dependant child column would be affected by that change, the result depends on the referential action.

Specify the referential action within ON UPDATE and ON DELETE subclauses of the FOREIGN KEYclause.

  • By default, the insert or delete operation fails and you receive an error. This is the same as specifying RESTRICT or NO ACTION.
  • If you use CASCADE, foreign keys propagate deletes or updates to all affected tables.
  • If you use SET NULL, values in the foreign key column(s) of the child table are set to NULL instead of the new parent key value.

The following example relates parent and child tables through a single-column primary key. The foreign key contains an ON DELETE CASCADE subclause to ensure that if you delete a record in the parent table, MySQL deletes any corresponding records in the child table:

CREATE TABLE parent ( 
id INT NOT NULL, 
PRIMARY KEY (id) 
) ENGINE=INNODB;

CREATE TABLE child (
 id INT, 
 parent_id INT, 
 INDEX par_ind (parent_id), 
 FOREIGN KEY (parent_id) 
     REFERENCES parent(id) 
	 ON DELETE CASCADE 
) ENGINE=INNODB;

You May Also Like