Deleting From Self-Referencing Tables

May 17th, 2012

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.

Self-Referencing Tables

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?

Solution

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();

Best Practices

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.

Previous: Profiling MySQL Queries Next: Selenium 2: Wrong Browser Launched