CODEWITHSUNDEEP
mysqldatabase
user1311784
user1311784

Reputation: 345

delete child rows of a table

If i have this structure:

table users

id_user
1
2

table lists

id_lists    name_list   users_id_user
    1           Work              1
    2           work              2

lists_has_users

users_id_user   lists_id_lists
    1                      2
    2                      1

What I want is, when delete the id_lists, I also want to delete all the users that are members of that list in list_has_users

For example if i delete the id_lists 1, the lists_id_lists 1 should also be deleted.

At the moment i get: Cannot delete or update a parent row

How solve this?

Upvotes: 1

Views: 3648

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270617

It seems as though you have a FOREIGN KEY constraint defined which enforces referential integrity, and prohibits you from deleting a parent row if children exist. Modify your foreign key to include ON DELETE CASCADE to force deletion of child rows.

Use DESCRIBE lists_has_users to get the FK's symbol if you don't know it.

ALTER TABLE lists_has_users DROP FOREIGN KEY `key_symbol`;
ALTER TABLE lists_has_users ADD FOREIGN KEY (lists_id_lists) REFERENCES lists (id_lists) ON DELETE CASCADE

You may want to add ON UPDATE CASCADE as well, so that the lists_has_users row changes if the id of lists ever changes.

Upvotes: 3

Andreas Linden
Andreas Linden

Reputation: 12721

Before deleting from lists you have to delete from lists_has_users as you seem to have integrity checks enabled.

Upvotes: 0

Related Questions