{"id":4860,"date":"2012-05-14T07:52:41","date_gmt":"2012-05-14T05:52:41","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4860"},"modified":"2012-05-14T07:52:41","modified_gmt":"2012-05-14T05:52:41","slug":"impact-of-foreign-keys-absence-on-replicating-slaves","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/impact-of-foreign-keys-absence-on-replicating-slaves","title":{"rendered":"Impact of foreign keys absence on replicating slaves"},"content":{"rendered":"<p>In this post I describe what happens when a slave&#8217;s Foreign Key setup is different from that of the master. I&#8217;m in particular interested in a setup where the slave has a subset of the master&#8217;s foreign keys, or no foreign keys at all. I wish to observe whether integrity holds.<\/p>\n<h4>Making the changes<\/h4>\n<p>Which foreign keys do we have and how do we drop them? If you want to do this by hand, well, good luck! Fortunately, <a href=\"http:\/\/code.google.com\/p\/common-schema\/\">common_schema<\/a> provides with quite a few handy views and routines to assist us. Consider viewing the existing foreign keys on <strong>sakila<\/strong>:<\/p>\n<blockquote>\n<pre>master&gt; SELECT <strong>create_statement<\/strong> FROM <strong>common_schema.sql_foreign_keys<\/strong> WHERE TABLE_SCHEMA='sakila';\r\n+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n| create_statement\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n| ALTER TABLE `sakila`.`address` ADD CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `sakila`.`city` (`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`city` ADD CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `sakila`.`country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `sakila`.`address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `sakila`.`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film` ADD CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `sakila`.`language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film` ADD CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `sakila`.`language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE |\r\n| ALTER TABLE `sakila`.`film_actor` ADD CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `sakila`.`actor` (`actor_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film_actor` ADD CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `sakila`.`film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film_category` ADD CONSTRAINT `fk_film_category_category` FOREIGN KEY (`category_id`) REFERENCES `sakila`.`category` (`category_id`) ON DELETE RESTRICT ON UPDATE CASCADE |\r\n| ALTER TABLE `sakila`.`film_category` ADD CONSTRAINT `fk_film_category_film` FOREIGN KEY (`film_id`) REFERENCES `sakila`.`film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`inventory` ADD CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `sakila`.`film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`inventory` ADD CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `sakila`.`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`payment` ADD CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `sakila`.`customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`payment` ADD CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `sakila`.`rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`payment` ADD CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `sakila`.`staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`rental` ADD CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `sakila`.`customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`rental` ADD CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `sakila`.`inventory` (`inventory_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`rental` ADD CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `sakila`.`staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`staff` ADD CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `sakila`.`address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`staff` ADD CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `sakila`.`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`store` ADD CONSTRAINT `fk_store_address` FOREIGN KEY (`address_id`) REFERENCES `sakila`.`address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`store` ADD CONSTRAINT `fk_store_staff` FOREIGN KEY (`manager_staff_id`) REFERENCES `sakila`.`staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n<p>Most of the foreign key constraints use <strong>RESTRICT<\/strong> for <strong>DELETE<\/strong> (meaning you are not allowed to delete a parent row when children exist), and <strong>CASCADE<\/strong> for <strong>UPDATE<\/strong> (meaning changes to parent will propagate to children). This is good, since I want to test behavior of both <strong>RESTRICT<\/strong> and <strong>CASCADE<\/strong>.<\/p>\n<p><!--more-->OK, we wish to remove these constraints from the slave. To see what we are going to do, consider:<\/p>\n<blockquote>\n<pre>slave1&gt; select <strong>drop_statement<\/strong> from <strong>common_schema.sql_foreign_keys<\/strong> where table_schema='sakila';\r\n+-----------------------------------------------------------------------------------+\r\n| drop_statement\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-----------------------------------------------------------------------------------+\r\n| ALTER TABLE `sakila`.`address` DROP FOREIGN KEY `fk_address_city`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`city` DROP FOREIGN KEY `fk_city_country`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`customer` DROP FOREIGN KEY `fk_customer_address`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`customer` DROP FOREIGN KEY `fk_customer_store`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film` DROP FOREIGN KEY `fk_film_language`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film` DROP FOREIGN KEY `fk_film_language_original`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film_actor` DROP FOREIGN KEY `fk_film_actor_actor`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film_actor` DROP FOREIGN KEY `fk_film_actor_film`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film_category` DROP FOREIGN KEY `fk_film_category_category` |\r\n| ALTER TABLE `sakila`.`film_category` DROP FOREIGN KEY `fk_film_category_film`\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`inventory` DROP FOREIGN KEY `fk_inventory_film`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`inventory` DROP FOREIGN KEY `fk_inventory_store`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`payment` DROP FOREIGN KEY `fk_payment_customer`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`payment` DROP FOREIGN KEY `fk_payment_rental`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`payment` DROP FOREIGN KEY `fk_payment_staff`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`rental` DROP FOREIGN KEY `fk_rental_customer`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`rental` DROP FOREIGN KEY `fk_rental_inventory`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`rental` DROP FOREIGN KEY `fk_rental_staff`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`staff` DROP FOREIGN KEY `fk_staff_address`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`staff` DROP FOREIGN KEY `fk_staff_store`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`store` DROP FOREIGN KEY `fk_store_address`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`store` DROP FOREIGN KEY `fk_store_staff`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-----------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n<p>To actually make the DROP, we use <em>common_schema<\/em>&#8216;s <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/eval.html\">eval()<\/a>:<\/p>\n<blockquote>\n<pre>slave1&gt; call <strong>common_schema.eval<\/strong>(\"select drop_statement from common_schema.sql_foreign_keys where table_schema='sakila'\");<\/pre>\n<\/blockquote>\n<p><em>eval()<\/em> is a handy routine which invokes statements generated by the given query.<\/p>\n<p>This concludes the setup part.<\/p>\n<p>Tests will include:<\/p>\n<ol>\n<li>Attempting to delete a parent row<\/li>\n<li>Attempting to add an invalid child row<\/li>\n<li>Attempting to update parent row<\/li>\n<\/ol>\n<p>I was thinking there would be a difference between the two binary log file formats: <strong>STATEMENT<\/strong> and <strong>ROW<\/strong>. But the tests I produced showed no difference.<\/p>\n<h4>Tests<\/h4>\n<p>Attempting to delete parent row:<\/p>\n<blockquote>\n<pre>master&gt; delete from actor where actor_id=1;\r\nERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`sakila`.`film_actor`, CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE)\r\n\r\nslave1&gt; select * from actor where actor_id=1;\r\n+----------+------------+-----------+---------------------+\r\n| actor_id | first_name | last_name | last_update\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----------+------------+-----------+---------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 | PENELOPE\u00a0\u00a0 | GUINESS\u00a0\u00a0 | 2006-02-15 04:34:33 |\r\n+----------+------------+-----------+---------------------+<\/pre>\n<\/blockquote>\n<p>Good: the master refused the <strong>DELETE<\/strong>, and no <strong>DELETE<\/strong> occurred on slave. Integrity is intact.<\/p>\n<p>Attempting to add an invalid child row:<\/p>\n<blockquote>\n<pre>master&gt; insert into film_actor (actor_id, film_id, last_update) values (9999, 1, NOW());\r\nERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sakila`.`film_actor`, CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE)\r\n\r\nslave&gt; select * from film_actor where actor_id=9999;\r\nEmpty set (0.00 sec)<\/pre>\n<\/blockquote>\n<p>Integrity is still intact.<\/p>\n<p>Attempting to update parent row: there is nothing invalid about this operation. I&#8217;m wondering whether changes are <strong>CASCADE<\/strong>d on slave as well as on master:<\/p>\n<blockquote>\n<pre>master&gt; update actor set actor_id=999 where actor_id=199;\r\n\r\nmaster&gt; select count(*) from film_actor where actor_id=999;\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 15 |\r\n+----------+<\/pre>\n<\/blockquote>\n<p>The <strong>999<\/strong> value wasn&#8217;t there before on the master, so this verifies the <strong>CASCADE<\/strong> works on master. As for slave:<\/p>\n<blockquote>\n<pre>slave&gt; select count(*) from actor where actor_id=999;\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+----------+\r\n\r\nslave&gt; select count(*) from film_actor where actor_id=999;\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+----------+<\/pre>\n<\/blockquote>\n<p>Bummer! The actor&#8217;s row was updated, but cascading did not work on slave.<\/p>\n<p>This is actually <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/innodb-and-mysql-replication.html\">documented<\/a>. However, the documentation only relates to the issue of slave tables being <strong>MyISAM<\/strong>. The problem occurs even when the slave tables are <strong>InnoDB<\/strong>, and have no foreign key constraints.<\/p>\n<h4>Conclusion<\/h4>\n<p>My personal interest in the scenario is due to something I&#8217;m working on, I&#8217;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.<\/p>\n<p>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 <a href=\"http:\/\/code.openark.org\/blog\/mysql\/triggers-use-case-compilation-part-i\">simulate their behavior<\/a>. Performance wise this is very bad.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this post I describe what happens when a slave&#8217;s Foreign Key setup is different from that of the master. I&#8217;m in particular interested in a setup where the slave has a subset of the master&#8217;s foreign keys, or no foreign keys at all. I wish to observe whether integrity holds. Making the changes Which [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[67,81],"class_list":["post-4860","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-data-integrity"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1go","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4860","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4860"}],"version-history":[{"count":25,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4860\/revisions"}],"predecessor-version":[{"id":4909,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4860\/revisions\/4909"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4860"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4860"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4860"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}