Let's look at this example database: as we can see, person depends on the city (person.city_id is a foreign key). i don't delete rows, i just set them inactive (active=0).. That is, this is 6.1.0-alpha not 6.1.0-alpha-debug. i start the server with mysqld --foreign-key-all-engines=1. i create a primary-key table and a foreign-key table with varbinary columns. the foreign-key table has an update cascade. i insert some values. i do some updates. the foreign-key table has a row which is not in the primary-key table.. There is a bug in on update cascade which forgets to pad the field in the child table with spaces. the child table becomes corrupt because a fixed length field is shorter than it should be! the fix will be in 4.0.14..
However, mysql provides a more effective way called on delete cascade referential action for a foreign key that allows you to delete data from child tables automatically when you delete the data from the parent table. mysql on delete cascade example. let’s take a look at an example of using mysql on delete cascade.. If a foreign key clause is defined on both tables in a foreign key relationship, making both tables a parent and child, an on update cascade or on delete cascade subclause defined for one foreign key clause must be defined for the other in order for cascading operations to succeed.. That's because the primary key value is never supposed to change. if, on the other hand, we have a foreign key referencing a unique column that is not a primary key, it may change occasionally..
