One of our support customers approached us with the following problem the other day:
1 2 3 4 5 6 7 | mysql> CREATE TABLE child_table ( `id` int unsigned auto_increment, `column1` varchar(64) NOT NULL, parent_id int unsigned NOT NULL, PRIMARY KEY (`id`), CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent_table (id)); ERROR 1215 (HY000): Cannot add foreign key constraint |
They could not create a table with an FK relation! So, of course, we asked to see the parent table definition, which was:
1 2 3 4 5 6 7 | CREATE TABLE `parent_table` ( `id` int unsigned auto_increment, `column1` varchar(64) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB PARTITION BY HASH (id) PARTITIONS 4; |
The parent table is partitioned! This immediately explained the problem; partitioned tables can not be part of an FK relationship, as described (in point 10) here – MySQL Error Code 1215: “Cannot add foreign key constraint”.
Quoting the official MySQL manual for completeness:
Partitioned tables using the InnoDB storage engine do not support foreign keys. More specifically, this means that the following two statements are true:
- No definition of an InnoDB table employing user-defined partitioning may contain foreign key references; no InnoDB table whose definition contains foreign key references may be partitioned.
- No InnoDB table definition may contain a foreign key reference to a user-partitioned table; no InnoDB table with user-defined partitioning may contain columns referenced by foreign keys.
So, after verifying it was impossible to guarantee referential integrity using CONSTRAINTs, we turned to an old alternative from MyISAM era of MySQL: using a set of triggers that would intercept the DML statements before they execute, and verify if the parent row actually exists.
So for this, we would create child_table without the constraint:
1 2 3 4 5 | CREATE TABLE child_table ( `id` int unsigned auto_increment, `column1` varchar(64) NOT NULL, parent_id int unsigned NOT NULL, PRIMARY KEY (`id`)); |
And then we create 4 triggers: BEFORE INSERT and BEFORE UPDATE on the child table, and BEFORE UPDATE and BEFORE DELETE on the parent table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DELIMITER // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_INS // CREATE TRIGGER PARTITIONED_TABLE_CHECK_INS BEFORE INSERT ON child_table FOR EACH ROW BEGIN DECLARE fk_check INT; DECLARE fk_error_msg VARCHAR(200); IF (@DISABLE_TRIGGERS IS NULL) THEN SELECT COUNT(*) FROM parent_table WHERE id=new.parent_id INTO fk_check; IF fk_check < 1 THEN SELECT CONCAT("Foreign key constraint fails for table `parent_table`, can't find row matching `id='", new.parent_id, "`") INTO fk_error_msg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg; END IF; END IF; END // |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_UPD // CREATE TRIGGER PARTITIONED_TABLE_CHECK_UPD BEFORE UPDATE ON child_table FOR EACH ROW BEGIN DECLARE fk_check INT; DECLARE fk_error_msg VARCHAR(200); IF (@DISABLE_TRIGGERS IS NULL) THEN SELECT COUNT(*) FROM parent_table WHERE id=new.parent_id INTO fk_check; IF fk_check < 1 THEN SELECT CONCAT("Foreign key constraint fails for table `child_table`, can't find row matching `id='", new.parent_id, "`") INTO fk_error_msg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg; END IF; END IF; END // |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- DELETE is checked the other way around and the trigger has to be attached to parent_table (I kept naming the same for consistency) DELIMITER // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_DEL // CREATE TRIGGER PARTITIONED_TABLE_CHECK_DEL BEFORE DELETE ON parent_table FOR EACH ROW BEGIN DECLARE fk_check INT; DECLARE fk_error_msg VARCHAR(200); IF (@DISABLE_TRIGGERS IS NULL) THEN SELECT COUNT(*) FROM child_table WHERE parent_id=old.id INTO fk_check; IF fk_check > 0 THEN SELECT CONCAT("Foreign key constraint fails for table `parent_table`, child table has ", fk_check," row(s) matching condition `parent_id='", old.id, "`") INTO fk_error_msg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg; END IF; END IF; END // |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- Check UPDATE on parent_id.id; If PK differs we must check it's not referenced DELIMITER // DROP TRIGGER IF EXISTS PARTITIONED_TABLE_CHECK_PARENT_PK_UPDATE // CREATE TRIGGER PARTITIONED_TABLE_CHECK_PARENT_PK_UPDATE BEFORE UPDATE ON parent_table FOR EACH ROW BEGIN DECLARE fk_check INT; DECLARE fk_error_msg VARCHAR(200); IF (@DISABLE_TRIGGERS IS NULL) THEN IF old.id <> new.id THEN SELECT COUNT(*) FROM child_table WHERE parent_id=old.id INTO fk_check; IF fk_check > 0 THEN SELECT CONCAT("Foreign key constraint fails for table `parent_table`, child table has ", fk_check," row(s) matching condition `parent_id='", old.id, "`") INTO fk_error_msg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = fk_error_msg; END IF; END IF; END IF; END // DELIMITER ; |
Testing the Triggers:
Populate parent_table:
1 2 | mysql> INSERT INTO parent_table (id, column1) VALUES (1, "column1"); Query OK, 1 row affected (0.03 sec) |
Test insert:
1 2 3 4 5 6 7 | -- Insert is valid mysql> INSERT INTO child_table (id, column1, parent_id) VALUES (null, "value1", 1); Query OK, 1 row affected (0.01 sec) -- Insert fails with FK check mysql> INSERT INTO child_table (id, column1, parent_id) VALUES (null, "value2", 2); ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, can't find row matching `id='2` |
So far so good! For valid child ids, inserts are accepted, and for invalid child ids, trigger rejects the insert.
Test Update:
1 2 3 | --Test invalid update on child: mysql> UPDATE child_table SET parent_id='2' WHERE parent_id='1'; ERROR 1644 (45000): Foreign key constraint fails for table `child_table`, can't find row matching `id='2` |
1 2 3 | -- Test invalid update on parent mysql> UPDATE parent_table SET id=5; ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, child table has 1 row(s) matching condition `parent_id='1` |
1 2 3 4 5 6 7 8 9 10 11 12 13 | -- Test valid update on parent and child mysql> INSERT INTO parent_table VALUES (10, "column1"); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO child_table VALUES (10, "column1", 1); Query OK, 1 row affected (0.01 sec) mysql> UPDATE parent_table SET id = 9 WHERE id = 10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE child_table SET parent_id = 9 WHERE id = 10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
Test Delete:
1 2 3 | -- Invalid delete mysql> DELETE FROM parent_table WHERE id=1 ; ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, child table has 1 row(s) matching condition `parent_id='1` |
For both delete and update, we also tested trigger is working as expecting and checking FK integrity.
Insert new row on parent_table which we should be able to delete without failing the “constraint” (as it will have no child rows) :
1 2 3 4 5 | -- Test valid delete INSERT INTO parent_table (id, column1) VALUES (2, "column2"); Query OK, 1 row affected (0.03 sec) mysql> DELETE FROM parent_table WHERE id=2 ; Query OK, 1 row affected (0.03 sec) |
Unfortunately, the non-standard REPLACE INTO is not compatible with the above method, as it actually consists of two operations – a DELETE and a subsequent INSERT INTO, and doing the DELETE on the parent table for a referenced row would trigger the FK error:
1 2 | mysql> REPLACE INTO parent_table (id, column1) VALUES (1, "column2"); ERROR 1644 (45000): Foreign key constraint fails for table `parent_table`, child table has 1 row(s) matching condition `parent_id='1` |
REPLACE INTO the child_table should work without issues.
On the other hand, INSERT…ON DUPLICATE KEY CHECK will work as expected as the trigger on the UPDATE will work correctly and prevent breaking referential integrity.
For convenience FK triggers can be disabled on the session; This would be the equivalent of SET foreign_key_checks=0. You can disable by setting the following variable:
1 2 | mysql > SET @DISABLE_TRIGGERS=1; Query OK, 0 rows affected (0.00 sec) |
Disclaimer:
The above is a proof of concept and while it should work for the vast majority of uses, there are two cases that are not checked by the triggers and will break referential integrity: TRUNCATE TABLE parent_table and DROP TABLE parent_table, as it will not execute the DELETE trigger and hence will allow all child rows to become invalid at once.
And in general, DDL operations which can break referential integrity (for example ALTER TABLE modifying column type or name) are not handled as these operations don’t fire TRIGGERs of any kind, and also it relies on you writing the correct query to find the parent rows (for example if you have a parent table with a multi-column primary key, you must check all the columns in the WHERE condition of the triggers)
Also when using “ON CASCADE DELETE” FKs, triggers won’t be fired on delete child rows. Keep this in mind if you have complex FK + triggers tables.
Last, keep in mind added performance impact; Triggers will add overhead, so please make sure to measure impact on the response time of the DML in these two tables.
Because of all the above, triggers are NOT suggested to be used to maintain data consistency, but using triggers is the only possible workaround when using partitioned tables with InnoDB. Please test thoroughly and be aware of the caveats before deploying to production!
And there is an issue about triggers does not fired when on delete cascade deletes child rows. Triggers are worst practices to hold data integrity.
Hi Balázs! Yes, you appreciation is correct. On FK “delete cascade”, triggers are not fired, so using triggers it NOT safe nor performant, but for partitioned tables it’s the only possible workaround at this moment.
I will add a note on the blogpost making this more clear
Hola Carlos, it is surprising that there is still no support for partitioned tables with FKs in InnoDB!!
Interesting article… do you have any performance comparison between FK relations vs Trigger relations?
I guess with Triggers they will have a worse behavior, even with all indexed keys..
Hi Jon, thanks for your message. I currently do not have any performance comparison between FK relations vs trigger performance, but might be a good follow up blogpost!
Triggers have many caveats and worse performance, when I have the chance I will benchmark how much worse using triggers is in performance terms
Error occurred during SQL query execution
Reason:
SQL Error [1064] [42000]: (conn=19) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘fk_check INT;
During trigger create I have faced above error. Unable to create any trigger. My MariaDB version is : 10.3.28-MariaDB
Hi Sheikh. I tested the above commands after removing the start space from each line and it worked for me. It seems the copy paste will break the code