1

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!

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
manxing
  • 3,165
  • 12
  • 45
  • 56

6 Answers6

8

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.

tdammers
  • 20,353
  • 1
  • 39
  • 56
1

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.

Travyguy9
  • 4,774
  • 8
  • 43
  • 63
1

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.

Stefan H
  • 6,635
  • 4
  • 24
  • 35
1

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.

spinon
  • 10,760
  • 5
  • 41
  • 59
0

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
Mohsen Kamrani
  • 7,177
  • 5
  • 42
  • 66
0

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.

Jim
  • 22,354
  • 6
  • 52
  • 80