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 upda开发者_高级运维te 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!

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.

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.

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.

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.

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.

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