Using Referential Constraints with Partitioned Tables in InnoDB

One of our support customers approached us with the following problem the other day:

They could not create a table with an FK relation! So, of course, we asked to see the parent table definition, which was:

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:

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.

 

Testing the Triggers:

Populate parent_table:

Test insert: