CODEWITHSUNDEEP
mysql
Joby Mathews
Joby Mathews

Reputation: 259

Unable to add foregin key in sql

I want to add a foreign key constraint to the table and I get this error message:

1452 - Cannot add or update a child row: a foreign key constraint fails t.#sql-156c_26f, CONSTRAINT fk_gt_sub FOREIGN KEY (package_category_id) REFERENCES gt_main (category_id))

Query:

ALTER TABLE gt_sub ADD CONSTRAINT fk_gt_sub FOREIGN KEY (package_category_id) REFERENCES gt_main(category_id)

Upvotes: 1

Views: 37

Answers (1)

user8608224
user8608224

Reputation:

This is because there are rows in the table gt_sub with category_id that doesn't exist in the other table gt_main.

The foreign key you are trying to add is created to prevent these type of rows from insertion, this is why it is failing to create the foreign key.

You can query these rows and then remove them to solve the issue:

SELECT *
FROM gt_sub
WHERE package_category_id NOT IN(SELECT category_id from gt_main)

Upvotes: 3

Related Questions