Foreign key relations in MySql – Avoiding accidental deletion of posts

Posted on July 7, 2009

1


The normal case when using foreign key relationships in database tables might be “ON DELETE CASCADE ON UPDATE CASCADE”. If you have a table connecting a user_id to an activity_id, this means that deleting the activity post also deletes the connection post. Fine! The activity is gone, and so the table post defining the connection between the (gone) activity_id and the user_id.

However, if you have a person table in wich you define the connection to, let’s say, city_id, and use a foreign key on that releationship, you’ve better watch up! What happens if you accidentally delete the current city? The “ON DELETE CASCADE” directive causes the deletion of that person too! And this might cause the deletion of importand information connected to that person… Big No No!

The solution lies in defining the relation “ON DELETE SET NULL”. This means that the person’s city_id is set to NULL, not that the person post is deleted. Much better! Pelase note that the city_id field has to be defined to accept NULL as an alternative, otherwise MySql won’t let you define that relationship.on

Advertisements
Tagged: ,
Posted in: Database