CODEWITHSUNDEEP
mysqlsqlforeign-keys
GluePear
GluePear

Reputation: 7715

A foreign key constraint fails when adding a foreign key

I have two pre-existing tables, bar (with 2 columns, id [int 11] and name) and foo_bar (with 3 columns, id, foo_id and bar_id [int 11]). (There's also a table foo but that's not relevant here.)

I want to add a migration to foo_bar so that when a row is deleted from bar it deletes the relevant row in foo_bar. This is my statement:

ALTER TABLE `foo_bar` ADD FOREIGN KEY (`bar_id`) REFERENCES `bar`(`id`) ON DELETE CASCADE

But when I run this I get this error:

1452 - Cannot add or update a child row: a foreign key constraint fails (dbn.#sql-1_10ad, CONSTRAINT #sql-1_10ad_ibfk_1 FOREIGN KEY (bar_id) REFERENCES bar (id) ON DELETE CASCADE)

Both bar_id and id on the bar table have the type of int(11). id on the bar table is a primary key.

Upvotes: 0

Views: 1929

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I think the issue is that rows already exist that violate the constraint. You can see if this is the case by running:

select fb.*
from foo_bar fb
where not exists (select 1 from bar b where b.id = fb.id);

If this is the case, then delete the offending rows and then try to add the constraint again.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

My guess is that there is a foreign key bar_id which is referring to an id in the bar table which does not exist. You may find it using this query:

SELECT *
FROM foo_bar fb
LEFT JOIN bar b
    on fb.bar_id = b.id
WHERE b.id IS NULL;

To fix the problem, you may either delete these problematical records from foo_bar or you can update them to point to parent rows in bar which actually exist.

Upvotes: 1

Related Questions