Home > cannot add or update a child row: a foreign key constraint fails

How to fix "Cannot add or update a child row: a foreign key constraint fails"

Alton Alexander
By Alton AlexanderUpdated on June 4th, 2022

The cannot add or update a child row: a foreign key constraint fails error code means that the foreign key value does not exist in the parent table. This can happen if the foreign key value is not set properly or if the parent table does not exist.

Other common searches for this error include:

  • error code: 1452. cannot add or update a child row: a foreign key constraint fails
  • cannot add or update a child row: a foreign key constraint fails

1. Ensure that the foreign key value exists in the parent table

when trying to add or update a child row in a table that has a foreign key constraint on it

  1. Look at the parent table and verify that there is a foreign key column in it that references the child table.
  2. If there is not a foreign key column in the parent table, create a foreign key column in the parent table and reference the child table.
  3. Set the constraint on the foreign key column in the parent table.
  4. Try to add or update a child row in the parent table. If the constraint fails, the problem is with the foreign key column in the parent table and you will need to fix it.

2. Ensure that the data types of the foreign key and the referenced key match

  1. Open the database where the foreign key is defined.
  2. Check the data type of the foreign key.
  3. If the data type of the foreign key is not the same as the data type of the referenced key, correct the data type of the foreign key.
  4. Check the data type of the referenced key.
  5. If the data type of the referenced key is not the same as the data type of the foreign key, correct the data type of the referenced key.

3. Ensure that the foreign key is not null

if the referenced row is null. If the referenced row is null, then the foreign key constraint fails. To fix a foreign key constraint that fails because the referenced row is null:

  1. Check the referenced row is not null. If it is null, then the foreign key constraint fails.
  2. Check the foreign key constraint definition. The foreign key constraint definition should specify a required column that is not null. If the column is not null, then the foreign key constraint succeeds.
  3. Update the foreign key constraint definition to specify a required column that is not null.
  4. Check the referenced row is not null. If it is not null, then the foreign key constraint succeeds.

4. Ensure that the foreign key does not have a default value

  1. Open the database where the foreign key resides.
  2. Locate the foreign key in the table.
  3. Check the column for a default value.
  4. If there is a default value, change it to the appropriate value.
  5. Click on the constraint icon next to the foreign key.
  6. On the Constraint dialog box, click on the Verify button.
  7. If the constraint fails, click on the Fix button.

If you didn't find success with an option above, then try:

  1. Ensure that the foreign key is not part of a composite key.
  2. Ensure that the foreign key is not part of a UNIQUE or PRIMARY KEY constraint.
  3. Ensure that the foreign key is not part of a CHECK constraint.
  4. Ensure that the foreign key is not part of a FOREIGN KEY constraint.
  5. Ensure that the foreign key is indexed.