In MySQL, can I defer referential integrity checks until commit

Question

As in this question, I've been reading PoEAA and wondering if it's possible to defer referential integrity checks until commit in MySQL.

I've run into this problem when wanting to insert a bunch of products and related products in the same commit. Even within a transaction, I get constraint errors when I try to insert into the related_products join table.

If it helps, I'm using PHP PDO for database connections.

I'd appreciate any help you could offer.

score 81 · Accepted Answer · edited Dec 10 '20 at 09:56

81

Looks like my answer is here...

Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.

Back to the drawing board.

edited Dec 10 '20 at 09:56

Alexander Azarov

12,971
2
50
54

answered Feb 16 '11 at 09:37

Ross McFarlane

4,054
4
36
52

3

@rossmcf +1 as I have been searching the documentation for this sentence :D – Songo Apr 02 '12 at 18:40
16

If this is an important feature for you, you can consider migrating to PostgreSQL which supports deferred constraints (including deferred unique constraints since 9.0) - and you'd get a lot of other nifty features as well (recursive queries, windowing functions, check constraints, ...) – Apr 03 '12 at 08:52
1

The feature request for this has been around since 2004: https://bugs.mysql.com/bug.php?id=7529 Any day now... – mae Apr 28 '19 at 22:42
1

The language appears to have moved to [point 3 here](https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-foreign-keys.html) – jswolf19 Apr 28 '20 at 02:16
@jswolf19 if I'm not mistaken, that's literally what he quoted in his answer – Brian Leishman Dec 16 '20 at 19:23
@BrianLeishman Yes, Alexander Azarov was kind enough to edit the post. – jswolf19 Dec 17 '20 at 06:05
@jswolf19 I see that now, I should have noticed the edit being so recent! – Brian Leishman Dec 17 '20 at 13:04

score 20 · Answer 2 · answered Feb 16 '11 at 10:59

20

If you are asking if MySQL supports the DEFERRABLE attribute for foreign keys (including the option INITIALLY DEFERRED) then the answer is a clear no.

You can't defer constraint checking until commit time in MySQL.

And - as you have already pointed out - they are always evaluated at "row level" not on "statement level".

answered Feb 16 '11 at 10:59

I still can't find anything related to this. Is the answer still a "NO" ? I am thinking of switching to postgresql for all the good reasons. – akki Mar 08 '16 at 10:30
@akki: yes, the answer is still NO. MySQL does not support deferrable constraints (or any other [modern SQL](http://modern-sql.com/slides) features) – Mar 08 '16 at 10:31

score 13 · Answer 3 · answered Apr 30 '12 at 14:14

13

You may handle this limitation of innodb engine, by temporarily disabling foreign key checks by setting server variable:

set foreign_key_checks=0;

From MySQL manual:

mysqldump also produces correct definitions of tables in the dump file, and does not forget about the foreign keys.

To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. It is also possible to set this variable manually:

mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;

answered Apr 30 '12 at 14:14

Thava

1,597
17
13

10

I'm not sure that this would still check the constraints after the update, though, which is what I was looking for. – Ross McFarlane Apr 30 '12 at 16:23
No, it does not and therefore this the option `foreign_key_checks=0` and similar options like disabled the uniqueness check are very, very, very dangerous. If it was so easy, then MySQL would probably also have implemented deferred constraints. These options are only meant to speed up backup and restore when you know for sure that no constraint will be violated. You must not use this option as a work-around for MySQL's lack of deferred constraint. If you re-enable the option, then no check is repeated. In particular, if the constraint is violated, then MySQL may show undefined behavior. – user2690527 Dec 20 '21 at 14:31

In MySQL, can I defer referential integrity checks until commit

3 Answers3

Linked