CODEWITHSUNDEEP
mysql
Miguel Stevens
Miguel Stevens

Reputation: 9211

Adding a foreign key contraint on a table with data

I'm wondering if it's possible to add a foreign key contraint on a table that already has data. I'm have a table Suppliers and a table Ingredients

When I try to add a constraint as follows:

enter image description here

I'm getting the following error

MySQL said: Cannot add or update a child row: a foreign key constraint fails (`blackfisk_relations`.`#sql-223_37`, CONSTRAINT `ingredients_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE)

enter image description here

Upvotes: 2

Views: 57

Answers (3)

Rags
Rags

Reputation: 309

If you want to add a constraint to a table with data in it, the data must satisfy the constraint (or what would be the point of adding a constraint, if the data didn't have to satisfy it?)

Your options:

  • You can remove the references to the foreign key that don't exist.
  • You can add the keys in the foreign key table so the references do exist.

(Some DBMS allow a constraint to be added with NOCHECK specified, which means the constraint will be added, but won't be applied to existing data - only new data and changes to existing data. I don't know if MySql allows this - SQL Server and Oracle do. If MySql does, you may have to script it, rather than using a GUI option.)

Upvotes: 1

Arslan ud Din Shafiq
Arslan ud Din Shafiq

Reputation: 340

If you have record against each primary key in table where you are creating foreign key then you can try to alter table.

Your error shows you have a record in table which does not exist as primary key in other table. This is preventing you from altering table.

Upvotes: 0

Anarxhy
Anarxhy

Reputation: 67

Of course you can, just ensure that the values of the foreign key exists on the referenced table, otherwise you will have to create all the missing values or changing their value to null

good luck

Upvotes: 1

Related Questions