MySQL – How to add a foreign key on new or existing table

TL; DR

How to add a foreign key to a new TABLE:

CREATE TABLE child (ID INT PRIMARY KEY, parent_ID INT, FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID));

How to add a foreign key to an existing TABLE:

ALTER TABLE child ADD FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID);

MySQL has the ability to enforce a record that exists on a parent table when you are adding/modifying data or validate that a record doesn’t exist when you are deleting data from your child table, leaving your database inconsistent. This is called Foreign Key. You can check the complete documentation here

As seen above, you can either create your table with an FK since the beginning or modify/alter your table to add a new constrain after table creation time. Apart from syntax to refer to a field on the parent table, you can control what will be the behavior when you UPDATE or DELETE a record on the PARENT table that has a reference to in on the child table. This is controlled by the optional parameter ON UPDATE and ON DELETE, the restrictions are as follow:

We will be using as an example the below table:

mysql> SELECT * FROM parent;
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM child;
+----+-----------+
| ID | parent_ID |
+----+-----------+
|  1 |         1 |
+----+-----------+
1 row in set (0.00 sec)
  • RESTRICT or NO ACTION – Default behavior when you omit ON UPDATE or ON DELETE, this means if you try to update the filed on the parent table that is referred to at the child table, your update/delete will be blocked:
    mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_ID`) REFERENCES `parent` (`ID`))
    

    Although not recommended, in extreme cases you can for MySQL to disable the FK check to by-passe above error:

    SET foreign_key_checks=0;

    Have in mind that this will despite the whole reason for having FK in the first place!

  • SET DEFAULT – It’s recognized by the parse (won´t give any error), however, its interpreted as RESTRICT.
  • CASCADE – Whatever action you do on the parent table, will replicate to the child table:
    mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT * FROM parent;
    +----+
    | ID |
    +----+
    |  2 |
    +----+
    1 row in set (0.00 sec)
    
    mysql> SELECT * FROM child;
    +----+-----------+
    | ID | parent_ID |
    +----+-----------+
    |  1 |         2 |
    +----+-----------+
    1 row in set (0.00 sec)
  • SET NULL – Whatever action you do on the parent table, child column will reset to NULL (make sure child filed is not set to NOT NULL):
    mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT * FROM parent;
    +----+
    | ID |
    +----+
    |  2 |
    +----+
    1 row in set (0.01 sec)
    
    mysql> SELECT * FROM child;
    +----+-----------+
    | ID | parent_ID |
    +----+-----------+
    |  1 |      NULL |
    +----+-----------+
    1 row in set (0.00 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *