Home > Deleting From Self-Referencing Tables

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.

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.

Tags:
  1. May 21st, 2012 at 16:47 | #1

    I just do ON DELETE CASCADE, that way deleting the parent cascades to all children, and I get rid of the entire tree.

  2. May 22nd, 2012 at 17:28 | #2

    I tried ON DELETE CASCADE, but it doesn’t work with MySQL and self-referencing tables. What version do you use? Maybe there’s a bug in my 5.1.37

  3. Matt
    October 4th, 2012 at 03:19 | #3

    Thanks a lot for this tips !

  1. No trackbacks yet.