CODEWITHSUNDEEP
sqlmysqlmysql-error-1451
manxing
manxing

Reputation: 3305

Can not delete a row in the table (MySQL)

Dear all, I have a problem in MySQL: I failed to execute DELETE FROM users where user_id ='1'; in the MySQL CLI. So I tried in the phpMyAdmin: Use the GUI to delete a row, I got this:

SQL query:

DELETE FROM `health_portal`.`users` WHERE `users`.`user_id` =1

MySQL said: Documentation

Cannot delete or update a parent row: a foreign key constraint fails (`health_portal`.`users`, CONSTRAINT `users_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`user_id`))

I looked up this error in the Mysql website and got: Error: 1451 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED_2)

Message: Cannot delete or update a parent row: a foreign key constraint fails (%s)

I don't know what is wrong here, hope anyone can give me a heads-up.

Many thx!

Upvotes: 1

Views: 20402

Answers (6)

Mohsen Kamrani
Mohsen Kamrani

Reputation: 7457

The reason is that you've already created a foreign key ('users_ibfk_2') that references a filed ('user_id') in the table which you want to delete a row from it. but as you've created a foreign key so removing the foreign key is not a good solution, so a better solution is (if you are using phpmyadmin):

  1. select the child table
  2. select 'Structure' tab
  3. go to 'Relation view'
  4. change the second drop-down list (ON DELETE) [in the row corresponding to 'doctor_id'] to 'CASCADE'

now by deleting a row in your parent table the corresponding row in the child table will be deleted.

if you are not using phpmyadmin :

  1. drop the foreign key
  2. add a new foreign key with ON DELETE cascade referential action instead

Upvotes: 0

tdammers
tdammers

Reputation: 20721

You have a doctor that references this user through a foreign key with on delete no action or on delete restrict. Either change the foreign key to on delete set null (which sets the referencing doctor's user_id to null) or on delete cascade (which deletes the doctor along with the user), or manually assign the doctor to a different user, then re-run your query.

Upvotes: 8

spinon
spinon

Reputation: 10847

The error is giving you the answer. There is a referential integrity link between the table you are trying to delete from and some other table. There was a constraint created to ensure that you don't accidentally delete the parent row leaving orphans behind.

If a doctor is always a user. Then you can't delete the user leaving behind a bunch of doctors with no user record.

Upvotes: 1

Jim
Jim

Reputation: 22656

This means that another table has a foreign key link to this one. You cannot delete the user with id 1 because another table references this user.

Upvotes: 0

Stefan H
Stefan H

Reputation: 6683

You have a table (doctors i assume) that references that row with a foreign key constraint. You would have to delete the doctor first in order to delete the user. Or else add an on delete cascade to the foreign key constraint.

Upvotes: 1

Travyguy9
Travyguy9

Reputation: 4344

There is another table that is using that user (with user_id of 1) and that row needs to be deleted before you can remove the user from the users table.

Upvotes: 1

Related Questions