Reputation: 1854
Although there are some similar questions about the subject, I can't find the right answer for my problem. I have 2 tables called customer
and car
. What I want to do is this: When I delete a customer from database, I want the car that belongs to that customer will be automatically deleted as well. The code that MySQL Workbench generated for me is this:
ALTER TABLE `autocare`.`car`
ADD CONSTRAINT `customerId`
FOREIGN KEY (`CUSTOMER_ID`)
REFERENCES `autocare`.`customer` (`ID`)
ON DELETE CASCADE
ON UPDATE RESTRICT;
And I get this error:
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
(`autocare`.`#sql-80c_388`, CONSTRAINT `customerId` FOREIGN KEY (`CUSTOMER_ID`)
REFERENCES `customer` (`ID`) ON DELETE CASCADE)
There was no relation between those tables before. Any ideas? Thanks in advance!
Upvotes: 1
Views: 179
Reputation: 270795
Your goal is ultimately to implement a cascading deletion from customer
to car
. When you attempt to add the constraint with the tables as they are now, it fails because the car
table must include rows having a a CUSTOMER_ID
value which does not currently exist in the parent customer
table.
You should first locate those orphan rows and delete them (since your goal is to delete them anyway). You can find them with a query like:
SELECT *
FROM car
WHERE
CUSTOMER_ID NOT IN (SELECT ID FROM customer)
Once the orphan records are removed, the foreign key constraint can be met by the remaining existing rows and your ALTER TABLE
statement will succeed.
Upvotes: 1