CODEWITHSUNDEEP
mysqlforeign-keysrelational-database
oxyt
oxyt

Reputation: 1854

MySQL: Alter table to set Foreign key

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

Answers (1)

Michael Berkowski
Michael Berkowski

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

Related Questions