MySQL Disable Foreign Key Checks

What and Why

Here are some test results regarding foreign key checks in mysql, and what actually happens to the data, when you change the relation type, eg CASCADE, SET NULL, RESTRICT, and NO ACTION.


I basically want to check whether foreign keys are processed on the table after the key checks are reenabled, or they are ignored completely, or if there is some other vooodoo that happens.

Method

I setup 2 Tables, T1 and T2, T1 has a primary key (id) and T2 has a foreign key (ref) which points to T1.id.

For the test I just did updates to the id in T1, and deleted a row in T1, and watched what happened to the data in T2, for each of the FK relations types.

The code used to disable the foreign key checks is:

SET foreign_key_checks = 0;
-- UPDATE `test1` set id=13 where id = 3;
DELETE FROM `test1` WHERE id = 3;
SET foreign_key_checks = 1;

I commented out the delete or update line as appropriate.
and then reset the tables after each test.

Results

Normal Operation
================
CASCADE on update = both records updated
CASCADE on delete = both records deleted
SET NULL on delete = row deleted and ref set to null
SET NULL on update = row updated and ref set to null
RESTRICT on delete = delete prevented
RESTRICT on update = update prevented
NO ACTION on update = update prevented
NO ACTION on delete = delete prevented

Disable FK Checks
=================
CASCADE on update = row updated and leaves dangling ref
CASCADE on delete = row deleted and leaves dangling ref
SET NULL on update = row updated and leaves dangling ref
SET NULL on delete = row deleted and leaves dangling ref
RESTRICT on update = row updated and leaves dangling ref
RESTRICT on delete = row deleted and leaves dangling ref
NO ACTION on update = row updated and leaves dangling ref
NO ACTION on delete = row deleted and leaves dangling ref

Conclusion

The most obvious conclusion, is that disabling foreign key checks as a  part of the update or delete process can leave invalid foreign keys in the database.  While there might be good reasons for doing this, the chance that the data will be left in an inconsistent state is very high.