ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails

Question

I have been searching for this error and stumbled upon a few questions of the same nature, but as i understand it, they seem to be concerned on UPDATING issue. Mine stems from DELETING of an entry.

Here's how my table is made of:

CREATE TABLE `product` (
  `product_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT
 'represents unique identifier for every existing products',
  `code` varchar(20) NOT NULL,
  `name` varchar(45) NOT NULL COMMENT 'description',
  `price` decimal(11,4) NOT NULL,
  `short_name` varchar(10) NOT NULL COMMENT 
'name that can be used quickly to referenc or immediately know what is the product',
  `count` bigint(19) unsigned NOT NULL DEFAULT '0',
  `product_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `is_active` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`product_id`),
  KEY `product_product_typeFK_idx` (`product_type_id`),
  CONSTRAINT `product_product_typeFK` FOREIGN KEY (`product_type_id`) REFERENCES
 `product_type` (`product_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

Then it also has some accompanying TRIGGER which inserts some data:

USE `RFVPOS`;
DELIMITER $$
CREATE TRIGGER `Product_BDEL` BEFORE DELETE ON `product` FOR EACH ROW

BEGIN
    INSERT INTO `product_audit`
    (product_id,
    code, 
    name, 
    short_name, 
    price,
    count,
    delete_user,
    delete_date
    )

    values
    (OLD.product_id,
    OLD.code,
    OLD.name,
    OLD.short_name,
    OLD.price,
    OLD.count,
    CURRENT_USER(),
    NOW()
    );
END

Here as well is the structure of 'product_audit':

CREATE TABLE `product_audit` (
  `product_audit_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` mediumint(8) unsigned NOT NULL,
  `code` varchar(20) NOT NULL,
  `name` varchar(45) NOT NULL,
  `price` decimal(11,4) NOT NULL,
  `short_name` varchar(10) NOT NULL,
  `count` bigint(19) unsigned NOT NULL,
  `delete_user` varchar(45) NOT NULL,
  `delete_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_audit_id`),
  KEY `product_audit_productFK_idx` (`product_id`),
  CONSTRAINT `product_audit_productFK` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

Then it flashes this error:

ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails

Now, what confuses me before was that, no other table entries have been using the entry that i am deleting on 'product' table. This delete should go smoothly.

So, i tried removing my TRIGGER on the 'product' table and BLAM, the delete was a success.
This means the error lies on my TRIGGER, can you help me point out where exactly (if not on the trigger) and WHY the error happened.

score 5 · Accepted Answer · edited Jan 24 '17 at 18:15

5

Before deleting the product your trigger will insert into product_audit. If the product_id column in product_audit is a foreign key to product, then you can't delete this row from product anymore, because it is a parent to the newly created row in product_audit.

Try to remove the foreign key constraint from product_audit.

Since you didn't show the table definition for product_audit, the above is guessing in that regard. (At the time of writing, that is. But my guess was correct!)

edited Jan 24 '17 at 18:15

7ochem

2,183
1
34
42

answered Nov 02 '14 at 06:40

wolfgangwalther

1,226
7
15

This answer made it clear. BTW, is it advisable to put constraints in product_audit? I mean, any table that catches deleted entries? – Fasev Moweasck Nov 02 '14 at 06:45
1

What you are trying to implement is a 'soft delete' or something like that. So you want do delete your entries from the database, but not completely - you want to back them up, right? In that case I would not move those entries to a different table, but I would create another column in your `product` table called `deleted`. Default value is 0 / false. Whenever you want to delete a row from `product` just set deleted to 1 / true. Whenever you select from `product` add `WHERE deleted=0` to your query. You could also create a view with that `WHERE` statement to your `product` table. – wolfgangwalther Nov 02 '14 at 06:50
Yep. Why i wanted to put it on another table is to avoid the table from growing large, affecting performance? i'm not so sure about what i said.. – Fasev Moweasck Nov 02 '14 at 06:52
1

If you fear your table will grow too much, you could also change your deleted column to a timestamp and allow `NULL` values. `NULL` would mean that this column is not deleted, yet. When deleting, set the column to `NOW()`. You could then every once in a while completely `DELETE` rows from your table, that have been deleted a certain time ago (i.e. more than a month ago, or something like that). You would still be able to recover rows, that have been deleted recently, but your table will not grow indefinitely. – wolfgangwalther Nov 02 '14 at 06:55
can you tell me how to do this. In my understanding this would mean, that i would hamper the delete action if the delete row is NULL else, delete? – Fasev Moweasck Nov 02 '14 at 07:05
There's also more on the topic here: http://stackoverflow.com/questions/5020568/soft-delete-best-practices-php-mysql or here: http://dba.stackexchange.com/questions/13134/how-to-implement-soft-deletes – wolfgangwalther Nov 02 '14 at 07:06
I didn't think about that yet, but that seems like a great approach to me. Create a trigger `ON DELETE`, that sets the timestamp and cancels the delete action, when the timestamp was not set, yet. When the timestamp is set already, delete the row completely. – wolfgangwalther Nov 02 '14 at 07:07
Ahh, reviewing my design.. I would need to have a more detailed info on the delete like who deleted it, i guess thats why i dedicated another table. But is there any big issue on using another table? – Fasev Moweasck Nov 02 '14 at 07:08
Using another table would require you to either drop the foreign key constraints OR backup / delete corresponding parent / child rows in other tables as well OR add foreign key constraints from your backup tables to the real tables. While the last option seems easy at first, what happens if you delete on of the parent / child rows? Will they be backed up as well? In a different table? All of a sudden, you will be running into trouble, **especially**, when trying to recover those deleted rows. I, personally, would stay away from that. – wolfgangwalther Nov 02 '14 at 07:12
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/64110/discussion-between-lee-jeong-and-wolfgangwalther). – Fasev Moweasck Nov 02 '14 at 07:32
1

I would suggest you create another question, in which you state your problem for soft deletion. Mention the different ways to do it, that you have already thought about and state what drawback you see in each of them and ask for advice on how to achieve your goals (specify them clearly) best! – wolfgangwalther Nov 02 '14 at 12:53

Payam · Answer 2 · 2014-11-02T06:42:57.473

The entry which you're trying to delete in your product table is a parent to some other table. Meaning that, if you try to delete an entry from your product table where product_id=1 you have to make sure that all the entries refrencing to this entry should be deleted first.

Let's assume you have a table called user

+--------+-------+--------+
| UserID | Name  | Gender |
+--------+-------+--------+
| 1      | Jason | Male   |
+--------+-------+--------+
| 2      | Sara  | Female |
+--------+-------+--------+
| 3      | John  | Male   |
+--------+-------+--------+

and you have a table user_address which is referencing to user table with user_id being foreign key.

+-----------+-----------+--------+
| AddressID | Address   | UserID |
+-----------+-----------+--------+
| 1         | Address A | 1      |
+-----------+-----------+--------+
| 2         | Address B | 1      |
+-----------+-----------+--------+
| 3         | Address C | 2      |
+-----------+-----------+--------+

Now if you want to run a delete query on userlike this:

delete from user where userID=1;

You have to make sure to delete all the children (dependencies) to it, which in this game is Address A and Address B.

**"Now, what confuses me before was that, no other table entries have been using the entry that i am deleting on 'product' table. This delete should go smoothly."** - as i said above. Is that the same as what you have stated? What do you mean parent to some other table? you mean, is a foreign key to some othertable? — Fasev Moweasck, Nov 02 '14 at 06:38

score 1 · Answer 3 · answered Nov 02 '14 at 06:49

You can check if product_id is used as FOREIGN KEY in any other table by running the follwing query.

SELECT    constraint_name,    table_name
 FROM    information_schema.table_constraints 
 WHERE  constraint_type = 'FOREIGN KEY'
  AND table_schema = DATABASE()
  AND constraint_name LIKE '%product_id%'
ORDER BY    constraint_name;`

ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails

3 Answers3