MySQL – Delete row that has a foreign key constraint which reference to itself

constraintdatabase-designdeleteforeign keyMySQL

I have a table in which I store all the forum messages posted by the users on my website. The messages hierarchy strucrue is implement using a Nested set model.

The following is a simplified structure of the table:

Id (PRIMARY KEY)
Owner_Id (FOREIGN KEY REFERENCES TO Id)
Parent_Id (FOREIGN KEY REFERENCES TO Id)
nleft
nright
nlevel

Now, the table is looking something like this:

+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| Id      | Owner_Id      | Parent_Id      | nleft      | nright      | nlevel      |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| 1       | 1             | NULL           | 1          | 8           | 1           |
| 2       | 1             | 1              | 2          | 5           | 2           |
| 3       | 1             | 2              | 3          | 4           | 3           |
| 4       | 1             | 1              | 6          | 7           | 2           |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +

Note that the first row is the root message, and the tree of this post can be displayed as:

-- SELECT * FROM forumTbl WHERE Owner_Id = 1 ORDER BY nleft;

MESSAGE (Id = 1)
    MESSAGE (Id = 2)
        Message (Id = 3)
    Message (Id = 4)

My problem occurs when I try to delete all the rows under the same Owner_Id in a single query. Example:

DELETE FROM forumTbl WHERE Owner_Id = 1 ORDER BY nright;

The above query fails with the following error:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (forumTbl, CONSTRAINT Owner_Id_frgn FOREIGN KEY (Owner_Id) REFERENCES forumTbl (Id) ON DELETE NO ACTION ON UPDATE NO ACTION)

The reason is that the first row, which is the root node (Id=1), also has the same value in its Owner_Id field (Owner_Id=1), and it causing the query to fail due to the foreign key constraint.

My question is: How can I prevent this foreign key constraint circularity and delete a row that reference to itself? Is there a way to do it without first having to update the the Owner_Id of the root row to NULL?

I created a demo of this scenario: http://sqlfiddle.com/#!9/fd1b1

Thank you.

Best Answer

Besides disabling foreign keys which is is dangerous and can lead to inconsistencies, there are two other options to consider:

Modify the FOREIGN KEY constraints with the ON DELETE CASCADE option. I haven't tested all cases but you surely need this for the (owner_id) foreign key and possibly for the other as well.

ALTER TABLE forum
    DROP FOREIGN KEY owner_id_frgn,
    DROP FOREIGN KEY parent_id_frgn ;
ALTER TABLE forum
    ADD CONSTRAINT owner_id_frgn
        FOREIGN KEY (owner_id) 
        REFERENCES forum (id)
        ON DELETE CASCADE,
    ADD CONSTRAINT parent_id_frgn
        FOREIGN KEY (parent_id) 
        REFERENCES forum (id)
        ON DELETE CASCADE ;

If you do this, then deleting a node and all the descendants from the tree is simpler. You delete a node and all descendants are deleted through the cascade actions:

DELETE FROM forum
WHERE id = 1 ;         -- deletes id=1 and all descendants

The issue that you stepped into is actually 2 issues. The first is that deleting from a table with self-referencing foreign key is not a serious problem for MySQL, as long as there is no row that references itself. If there is a row, as in your example, the options are limited. Either disable foreign keys or use CASCADE action. But if there are no such rows, deleting becomes a smaller problem.

So, if we decide to store NULL instead of the same id in owner_id, then you could delete without disabling foreign keys and without cascades.

You would then stumble upon the second problem! Running your query would raise a similar error:
```
DELETE FROM forum 
WHERE owner_id = 1 OR id = 1 ; 
```
Error(s), warning(s):
Cannot delete or update a parent row: a foreign key constraint fails (rextester.forum, CONSTRAINT owner_id_frgn FOREIGN KEY (owner_Id) REFERENCES forum (id))

The reason for this error would be different than before though. It's because MySQL checks each constraint after every row is deleted and not (as it should) at th eend of the statement. So when a parent is deleted before its child is deleted, we get a foreign key constraint error.

Fortunately, there is a simple solution for this, thnx to the nested set model and to that MySQL allows us to set an order for the deletes. We just have to order by nleft DESC or by nright DESC, which makes sure that all the children are deleted before a parent:
```
DELETE FROM forum 
WHERE owner_id = 1 OR id = 1 
ORDER BY nleft DESC ; 
```
Minor note, we could (or should) use a condition that considers the nested model as well. This is equivalent (and might use an index on (nleft, nright) to find which nodes to delete:
```
DELETE FROM forum 
WHERE nleft >= 1 AND nright <= 8 
ORDER BY nleft DESC ; 
```

Related Solutions

Mysql – Foreign Key Constraint fails

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table. Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks

MySQL: update instead of delete if foreign key constraint

You can't directly do it since there's no way (that I can think of) to alternately UPDATE or DELETE from the same query... generally a query can only do one type of CRUD operation (ON DUPLICATE KEY UPDATE being an exception to this).

You could, however, make it "feel like" a single query, with a stored procedure, where you'd set up a CONTINUE HANDLER for the foreign key error you anticipate. The handler will trap the error and can be used to set a variable which you can test to see whether you hit the error and therefore need to also try the update query.

DELIMITER $$

DROP PROCEDURE IF EXISTS delete_or_update $$
CREATE PROCEDURE delete_or_update (IN input_value INT)
BEGIN

  DECLARE hit_fk_error TINYINT DEFAULT 0;
  DECLARE deleted_row_count INT DEFAULT NULL;
  DECLARE updated_row_count INT DEFAULT NULL;

  -- 1451 is probably the error code that will be generated
  -- Cannot delete or update a parent row: a foreign key constraint fails (%s)
  -- otherwise, you may need to substitute the correct error code in the next line

  DECLARE CONTINUE HANDLER FOR 1451 SET hit_fk_error = TRUE;

  DELETE FROM parent_table WHERE tested_column = input_value; /* arg to the stored proc */
  SET deleted_row_count = ROW_COUNT();

  IF (hit_fk_error = TRUE) THEN
    UPDATE parent_table SET some_other_value = 'something_else'
     WHERE tested_column = input_value;
    SET updated_row_count = ROW_COUNT();
  END If;

  SELECT deleted_row_count, updated_row_count, hit_fk_error;

END $$

DELIMITER ;

This will try the delete; if it hits a foreign key error it will then try the update (and the foreign key error will be suppressed). The procedure will return a record set with the number of rows affected by each query and whether the foreign key error occurred. If the "input_value" isn't an INT or the WHERE clause is more complex, you'll need to modify the structure, accordingly.

The potential problem I see here is that if the where clause is too broad, and matches some rows that would cause a foreign key error and other rows that wouldn't, then the query will not treat the rows individually. That could be accomplished by a more sophisticated procedure that used a cursor to find the rows in the where clause and tried to delete or update them individually by primary key after identifying them. That approach would be less efficient but more precise.

Best Answer

Related Solutions

Mysql – Foreign Key Constraint fails

MySQL: update instead of delete if foreign key constraint

Related Question