Foreign key constraints are a fantastic way of assuring data integrity within your database. What does that actually mean. It means that if you have two tables (foreign key constraints only work on InnoDB storage tables):
CREATE TABLE depts ( deptid INT auto_increment, deptname VARCHAR(255), PRIMARY KEY (deptid)) ENGINE = InnoDB; CREATE TABLE employee ( empid INT auto_increment, name VARCHAR(255), deptid INT, PRIMARY KEY (empid), FOREIGN KEY (deptid) REFERENCES depts(deptid) ON DELETE CASCADE) ENGINE = InnoDB;
Basically everytime we add an entry in the employee table, the database internally will manage that the value placed in deptid will have a matching value in the depts table.
Here is an example of this working:
mysql> INSERT INTO depts(deptname) VALUES ('payrole');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO depts(deptname) VALUES ('IT');
Query OK, 1 row affected (0.00 sec)
mysql> select * from depts;
+--------+----------+
| deptid | deptname |
+--------+----------+
| 1 | payrole |
| 2 | IT |
+--------+----------+
2 rows in set (0.02 sec)
mysql> INSERT INTO employee (name, deptid) VALUES ('joe',1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO employee (name, deptid) VALUES ('dave',2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO employee (name, deptid) VALUES ('ron',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `depts` (`deptid`) ON DELETE CASCADE)
The CASCADE ON DELETE clause implies that if a department is removed then all employee entries that refer to this are also removed.
mysql> select * from employee; +-------+------+--------+ | empid | name | deptid | +-------+------+--------+ | 1 | joe | 1 | | 2 | dave | 2 | +-------+------+--------+ 2 rows in set (0.00 sec) mysql> select * from depts; +--------+----------+ | deptid | deptname | +--------+----------+ | 1 | payrole | | 2 | IT | +--------+----------+ 2 rows in set (0.00 sec) mysql> delete from depts where deptid = 1; Query OK, 1 row affected (0.00 sec) mysql> select * from depts; +--------+----------+ | deptid | deptname | +--------+----------+ | 2 | IT | +--------+----------+ 1 row in set (0.00 sec) mysql> select * from employee; +-------+------+--------+ | empid | name | deptid | +-------+------+--------+ | 2 | dave | 2 | +-------+------+--------+ 1 row in set (0.00 sec)
While foreign key constraints are ideal in making sure that you have data integrity in the database it comes at a performance expense. Every insert or update into the employee table above means that a subsequent access to the department table. For online transaction processing (OLTP) transactions the constraint merit the performance overhead which should be negligible. OLTP transactions are short transactions such as those populating an online form. However, in large reporting/warehouse or DDS databases where large volumes of data are loaded periodically, the overhead of constantly checking the depts table will result in much larger load times. The type of data loaded in DDS type databases are relatively static with primarily INSERT and DELETE statements. A general approach for these type of databases is NOT to have foreign key constraints but to load the data into a staging table and after validating the integrity of the data in the database then to copy the data to the primary table. This approach allows for faster loading of the data into the database and also allow for the data to be validated or corrected prior to loading it into the primary table.