MySQL::Foreign Keys and Referential Integrity

Today we are going to continue with exploring those “rarely used” MySQL features. In previous post we were talking about MySQL Transactions and how to use them. Today our focus is on Foreign Keys and Referential Integrity.

Like with transactions, referential integrity support is not available in MyISAM tables, so InnoDB tables has to be used. Many developers probably don’t know that this feature is available since version 3.23.44, which is available since 2001. Anyway, let’s go with a (very) little theory, then we are going to move to some practice.

A foreign key (FK) is a field or group of fields in a database record that point to a key field or group of fields forming a key of another database record in some (usually different) table. The rule of “Referential Integrity” says that foreign key must not have a value which doesn’t exist in table where it refers to. That’s all about theory from me (I told you it will be very short :). If you need more theory feel free to use google for more informations. Now lets see some examples.

Imagine a database with cell phones, which among other things contains tables with phone vendors and phones. Let say something like this:

Vendors
---------------------
ID     Vendor
1.     Nokia
2.     Sony-Ericson
3.     Samsung
4.     Siemens

Phones
-------------------------------
ID.     Model     VID
1.      6600       1
2.      6630       1
3.      3650       1
4.      p800       2
5.      p900       2
...

VID column in ‘phones’ table is in direct relation with ID column of table ‘vendors’, in other words in this particular case VID column is so called foreign key. VID values in our example are related with smarth phones of vendors Nokia (6600, 6630 i 3650) and Sony-Ericson (p800, p900).
In order to keep so called referential integrity of our tables, we need to make sure that values of VID columns of table phones contains only those values that exists in ID column of vendors table. In case we remove some particular vendor from first table, all records referencing to it from phones table must be removed as well (this is known as cascade delete). Simmilar stands for update and insert operations, which means if we update ID in first table, foreign key VID must be updated too. Also, during inserting records into 2nd (phones) table we need to take care that VID value have proper reference in ID column of vendors table. If your RDBMS doesn’t have support for Referential Integrity you have to take care about all these things. Fortunately, by using MySQL InnoDB tables, we can let MySQL do da job for us.

Foreign Key is set during initial table creation. Syntax looks like this:

[CONSTRAINT symbol]
FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

– CONSTRAINT symbol is optional constraint name for this foreign key.
– FOREIGN KEY stands for table column which represent foreign key
– REFERENCES tbl_name (index_col_name, …) stands for table and exact column where foreign key is referencing
ON DELETE and ON UPDATE defines actions that will be taken in case of delete/update in main table. Possible values are:
– RESTRICT deleting records in main table will be refused if there are foreign keys in 2nd table referencing to them
– CASCADEdeleting records in main table will result in deleting related records in 2nd table (table with foreign key)
– SET NULL simmilar to CASCADE except that records in 2nd table will not be removed, they will be set to NULL instead

In order to demonstrate concept, I am going to create database with cell phones I mentioned above.

create table vendors(
	id int unsigned primary key not null auto_increment,
	vendor varchar(255) not null unique
	) engine = InnoDB;


create table phones(
	id int unsigned primary key not null auto_increment,
	model varchar(255) not null,
	vid int unsigned not null,
	foreign key(vid) references vendors(id)
		on delete cascade
		on update cascade
	) engine = InnoDB;

insert into vendors(vendor) values ('Nokia'),('Sony-Ericson'),('Samsung'),('Siemens');
insert into phones (model,vid) values ('6600',1),('6630',1),('3650',1),('p800',2),('p900',2);

In this example we used option cascade, which means that deleting any record in vendors table will result in removing all records from phones table that refer to them. Now, let try to do some “cheating”, by trying to insert phone with unexistant vendor ID:

mysql> select * from vendors;
+----+--------------+
| id | vendor  |
+----+--------------+
|  1 | Nokia        |
|  3 | Samsung      |
|  4 | Siemens      |
|  2 | Sony-Ericson |
+----+--------------+
4 rows in set (0.00 sec)

mysql> select * from phones;
+----+-------+-----+
| id | model | vid |
+----+-------+-----+
|  1 | 6600  |   1 |
|  2 | 6630  |   1 |
|  3 | 3650  |   1 |
|  4 | p800  |   2 |
|  5 | p900  |   2 |
+----+-------+-----+
5 rows in set (0.02 sec)

mysql> insert into phones(model,vid) values ('unexistant',5);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql>

As you see, it didn’t work. We’ve tried to insert new phone model into phones table, but with vendor id (vid) 5 which doesn’t exist in vendor table, so MySQL complained in order to keep referential integrity of database.

Now, let see how cascade delete/update works:

mysql> update vendors set id=5 where vendor='sony-ericson';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from vendors;
+----+--------------+
| id | vendor  |
+----+--------------+
|  1 | Nokia        |
|  3 | Samsung      |
|  4 | Siemens      |
|  5 | Sony-Ericson |
+----+--------------+
4 rows in set (0.00 sec)

mysql> select * from phones;
+----+-------+-----+
| id | model | vid |
+----+-------+-----+
|  1 | 6600  |   1 |
|  2 | 6630  |   1 |
|  3 | 3650  |   1 |
|  4 | p800  |   5 |
|  5 | p900  |   5 |
+----+-------+-----+
5 rows in set (0.00 sec)
mysql>

So, we’ve updated vendor id (vid in vendors table) for ‘sony-ericsson’ to value 5. Thanks to on update cascade options we used during table creation, value of changed field is also updated for all ‘sony-ericsson’ phones. Simmilar would happen if we try to delete some vendor, let’s try Nokia:

mysql> select * from vendors;
+----+--------------+
| id | vendor  |
+----+--------------+
|  1 | Nokia        |
|  3 | Samsung      |
|  4 | Siemens      |
|  5 | Sony-Ericson |
+----+--------------+
4 rows in set (0.00 sec)

mysql> select * from phones;
+----+-------+-----+
| id | model | vid |
+----+-------+-----+
|  1 | 6600  |   1 |
|  2 | 6630  |   1 |
|  3 | 3650  |   1 |
|  4 | p800  |   5 |
|  5 | p900  |   5 |
+----+-------+-----+
5 rows in set (0.00 sec)

mysql> delete from vendors where vendor='Nokia';
Query OK, 1 row affected (0.03 sec)

mysql> select * from vendors;
+----+--------------+
| id | vendor  |
+----+--------------+
|  3 | Samsung      |
|  4 | Siemens      |
|  5 | Sony-Ericson |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select * from phones;
+----+-------+-----+
| id | model | vid |
+----+-------+-----+
|  4 | p800  |   5 |
|  5 | p900  |   5 |
+----+-------+-----+
2 rows in set (0.00 sec)
mysql>

So, as you see all precious Nokia phones are deleted from phones table, since we’ve removed Nokia from vendors table.

This is all for today. I think this tutorial should be enough for introduction, for more information you should consult proper section of MySQL manual.