In this post I describe what happens when a slave's Foreign Key setup is different from that of the master. I'm in particular interested in a setup where the slave has a subset of the master's foreign keys, or no foreign keys at all. I wish to observe whether integrity holds.

Making the changes

Which foreign keys do we have and how do we drop them? If you want to do this by hand, well, good luck! Fortunately, common_schema provides with quite a few handy views and routines to assist us. Consider viewing the existing foreign keys on sakila:

Most of the foreign key constraints use RESTRICT for DELETE (meaning you are not allowed to delete a parent row when children exist), and CASCADE for UPDATE (meaning changes to parent will propagate to children). This is good, since I want to test behavior of both RESTRICT and CASCADE.

OK, we wish to remove these constraints from the slave. To see what we are going to do, consider:

Bummer! The actor's row was updated, but cascading did not work on slave.

This is actually documented. However, the documentation only relates to the issue of slave tables being MyISAM. The problem occurs even when the slave tables are InnoDB, and have no foreign key constraints.

Conclusion

My personal interest in the scenario is due to something I'm working on, I'll elaborate on a future post. People sometime hope to get rid of foreign keys, and might wonder whether replication performance would boost having constraints removed on slaves.

When slave does not enforce foreign keys, you cannot rely on integrity with cascading constraints. An ugly patch might be to use triggers so as to simulate their behavior. Performance wise this is very bad.