Foreign Keys and MySQL_MySQL

foreign keys are often a mystery to new dbas in the mysql world. hopefully this blog will clear some of this up.

In this example, we will have a table for employee data and a table for the data on offices. First we need the two tables.
CREATE TABLE employee (
-> e_id INT NOT NULL,
-> name CHAR(20),
-> PRIMARY KEY (e_id)
-> );

Those who do not use Foreign Keys will not be familiar with the last four lines of the building table. Thetrickis that there are twoe_idcolumns, one in each table. In theemployee tableis it simply the employee identification number. However inbuilding table, it is declared to be a foreign key to theemployee tableusing thee_idcolumn. The CASCADE lines are telling MySQL that any UPDATEs or DELETEs on thee_idcolumn inemployee tablewill also be made on the corresponding row(s) in thebuilding table.

Add in some data .
mysql> INSERT INTO employee VALUES (10,'Larry'), (20,'Shemp'), (40,'Moe');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> INSERT INTO building VALUES (100,'Corner Office',10), (101,'Lobby',40);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM employee;
+------+-------+
| e_id | name |
+------+-------+
| 10 | Larry |
| 20 | Shemp |
| 40 | Moe |
+------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM building;
+------------+---------------+------+
| office_nbr | description | e_id |
+------------+---------------+------+
| 100 | Corner Office | 10 |
| 101 | Lobby | 40 |
+------------+---------------+------+
2 rows in set (0.00 sec)

Simple so far, right? So let us join theemployeetable with thebuildingtable.mysql> SELECT * FROM employee JOIN building ON (employee.e_id=building.e_id);
+------+-------+------------+---------------+------+
| e_id | name | office_nbr | description | e_id |
+------+-------+------------+---------------+------+
| 10 | Larry | 100 | Corner Office | 10 |
| 40 | Moe | 101 | Lobby | 40 |
+------+-------+------------+---------------+------+
2 rows in set (0.02 sec)


But we have three employees and only two lines of output? What happened? Well, what happened is that the query wanted the matches from both tables. To get all the rows from the first table and any matches from the second table, use aLEFT JOIN.
mysql> SELECT * FROM employee LEFT JOIN building ON (employee.e_id=building.e_id);
+------+-------+------------+---------------+------+
| e_id | name | office_nbr | description | e_id |
+------+-------+------------+---------------+------+
| 10 | Larry | 100 | Corner Office | 10 |
| 40 | Moe | 101 | Lobby | 40 |
| 20 | Shemp | NULL | NULL | NULL |
+------+-------+------------+---------------+------+
3 rows in set (0.00 sec)

Much better.

A big benefit of using foreign keys is that bad values get a lot harder to insert into the database. Try to add an office for a non-existent employ number 77.
mysql> INSERT INTO building VALUES (120,'Cubicle',77);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`building`, CONSTRAINT `building_ibfk_1` FOREIGN KEY (`e_id`) REFERENCES `employee` (`e_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql>


Now back to all that CASCADE stuff. Remove any of the employees from theemployee tableand the corresponding building entry will be removed.
mysql> DELETE FROM employee WHERE e_id=40;
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM employee LEFT JOIN building ON (employee.e_id=building.e_id);
+------+-------+------------+---------------+------+
| e_id | name | office_nbr | description | e_id |
+------+-------+------------+---------------+------+
| 10 | Larry | 100 | Corner Office | 10 |
| 20 | Shemp | NULL | NULL | NULL |
+------+-------+------------+---------------+------+
2 rows in set (0.00 sec)

Likewise changes are cascaded from theemployee tableto thebuilding table.
mysql> UPDATE employee SET e_id=21 WHERE e_id=20;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM employee LEFT JOIN building ON (employee.e_id=building.e_id);
+------+-------+------------+---------------+------+
| e_id | name | office_nbr | description | e_id |
+------+-------+------------+---------------+------+
| 10 | Larry | 100 | Corner Office | 10 |
| 21 | Shemp | NULL | NULL | NULL |
+------+-------+------------+---------------+------+
2 rows in set (0.00 sec)

There are many MySQL DBAs who do not use Foreign Keys for various reasons but they can be very handy. I find them useful in one to many relationships where I do not want to have to purge or change the many directly in a query.