Deleting From Self-Referencing Tables
Today, I ran into a common issue when deleting records from a table. I was using Doctrine 1.2 on top of MySQL. Deleting a certain number of records failed due to an integrity constraint.
A self-referencing table is one that has a foreign key pointing to the same table. For example, a folder hierarchy might have a parent_id column referring to its parent folder. Deleting a single row that has children should normally fail. But what if you wanted to delete a whole tree at once? Would you go recursively from the bottom up?
A simple way to achieve this is to use the following statement before: SET FOREIGN_KEY_CHECKS=0; This will prevent the MySQL database from complaining about the integrity constraint.
To achieve this using Doctrine, you can execute a raw query using the currently opened connection. Use the table name from which you expect to delete later, to make sure that the right connection is used.
Doctrine::getTable("TableName") ->getConnection() ->getDbh() ->query("SET FOREIGN_KEY_CHECKS=0;");
Now run your delete queries. Don’t forget to SET FOREIGN_KEY_CHECKS=1; at the end if you expect to execute more queries later on using the same connection.
Here’s an example of a delete query with Doctrine:
Doctrine_Query::create() ->delete() ->from("Folder") ->where("id > 25") ->execute();
You add database constraints for a reason and pointing to inexistant records compromises your data’s integrity. This approach assumes that you know in advance that removing the row set is alright. I only use this method in maintenance scripts, not in the normal application flow.