Is it possible to break the referential integrity when a foreign key constraint is set on an InnoDB table? The answer is an (unfortunate?) yes.
A little example demonstrates this:
mysql> CREATE TABLE t1 (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 (id) VALUES
-> (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE t2 (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> id_t1 INT NOT NULL,
-> FOREIGN KEY (id_t1) REFERENCES t1(id))
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 (id_t1) VALUES
-> (3), (2), (2), (3), (1);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1; SELECT * FROM t2;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
+----+-------+
| id | id_t1 |
+----+-------+
| 5 | 1 |
| 2 | 2 |
| 3 | 2 |
| 1 | 3 |
| 4 | 3 |
+----+-------+
5 rows in set (0.00 sec)
mysql> INSERT INTO t2 (id_t1) VALUES (4);
ERROR 1452 (23000): Cannot add or update a
child row: a foreign key constraint fails
(`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY
(`id_t1`) REFERENCES `t1` (`id`))
mysql> SET @@foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 (id_t1) VALUES
-> (2), (1), (4), (2);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SET @@foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1; SELECT * FROM t2;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
+----+-------+
| id | id_t1 |
+----+-------+
| 5 | 1 |
| 7 | 1 |
| 2 | 2 |
| 3 | 2 |
| 6 | 2 |
| 9 | 2 |
| 1 | 3 |
| 4 | 3 |
| 8 | 4 |
+----+-------+
9 rows in set (0.00 sec)
The record no 8 in table t2 has now an invalid value, due to the fact that we have set the foreign key verification temporarily to off (0).
It’s not a bad feature that it’s possible to temporarily turn @@foreign_key_checks off, because sometimes it’s necessary to change data in a way that referential integrity needs to be broken during the modification process. But most of the time, the data should be valid, when modification is finished. So what can be done?
Unfortunately, the only way is to check manually, if there are any invalid values:
mysql> SELECT t2.id
-> FROM t2 LEFT JOIN t1 ON t2.id_t1 = t1.id
-> WHERE t1.id IS NULL;
+----+
| id |
+----+
| 8 |
+----+
1 row in set (0.00 sec)
Could the command “SET @@foreign_key_checks = 1” verify automatically, whether all values are valid? Probably not, because all data from all tables with foreign key constraints would have to be verified for invalid data, because the command doesn’t know, which tables have been modified in the meantime. How else can this problem be solved?
PostgreSQL knows a clause that can be added to the FOREIGN KEY definition – it’s called INITIALLY DEFERRED. This clause allows foreign keys to be broken within a transaction, but they must be valid again, when the transaction commits. A nice feature, I think.
Another solution could be a table based command that includes this kind of verification. Maybe the CHECK TABLE [tablename] would be an appropriate command for checking foreign key integrity on InnoDB tables, too. The option EXTENDED can be appended to the command, but also this doesn’t work for broken integrity:
mysql> CHECK TABLE t2 EXTENDED; +---------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+----------+ | test.t2 | check | status | OK | +---------+-------+----------+----------+ 1 row in set (0.00 sec)
Maybe that’s something to be considered to add foreign key integrity checks to this (or maybe another) command?!