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