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:

So far so good! For valid child ids, inserts are accepted, and for invalid child ids, trigger rejects the insert.

Test Update:

Test Delete:

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) :

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:

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:

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!

Share this post

Comments (4)

  • Balázs Csaba Reply

    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.

    December 26, 2019 at 5:04 pm
    • Carlos Tutte Reply

      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

      December 27, 2019 at 11:27 am
  • Jon Reply

    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..

    December 27, 2019 at 7:04 am
    • Carlos Tutte Reply

      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

      December 27, 2019 at 10:56 am

Leave a Reply