CODEWITHSUNDEEP
mysqlsqlsql-delete
stack
stack

Reputation: 33

MySQL - How to delete related rows in a many to many relationship

For example if a customer is deleted, all the related row on other table(S) will be deleted. How to do that mysql, it's a hours i tried many solution but noone worked.enter image description here

Upvotes: 2

Views: 11604

Answers (2)

Cristian Gonçalves
Cristian Gonçalves

Reputation: 912

The easiest way to achieve that is design related tables with ON DELETE CASCADE option.

Basically, when you design a database table that has a foreign key (primary key on other table), you can set ON DELETE CASCADE for that foreign key. That means that when a record is deleted on the primary table (the table where this foreign key is the primary key) all related records on other tables are also deleted.

Therefore, you don't need to think on create "crazy" queries to delete related data on multiple tables.

You can learn more about ON DELETE CASCADE here

Upvotes: 3

cmnardi
cmnardi

Reputation: 1111

You have to DELETE the Address first, the Customers, and after the relation Or you can try to use DELETE cascate ... but be careful http://www.mysqltutorial.org/mysql-on-delete-cascade/ see this too : MySQL on delete cascade. Test Example

DELETE FROM Addresses WHERE AddressID IN (SELECT AddressID FROM Custormers_Addresses WHERE CustormerID = ID)

THEN .. Delete the customer DELETE FROM Customers WHERE CustomerID = ID

THEN .. delete the relacion DELETE AddressID FROM Custormers_Addresses WHERE CustormerID = ID

Upvotes: 2

Related Questions