The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Question

I am trying to add a new foreign key to an existing table where there is data in the column I am wanting to make a change to.

In dev, I have tried this where data does and does not exist. Where there is no data this works fine.

ALTER TABLE [rpt].ReportLessonCompetency WITH CHECK
ADD CONSTRAINT [FK_Grade_TraineeGrade_Id]
FOREIGN KEY (Grade) REFERENCES [rpt].TraineeGrade(Id)

Where there is data I get the following error

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Grade_TraineeGrade_Id". The conflict occurred in database "T_test", table "Core.Report.TraineeGrade", column 'Id'.

I would be grareful if someone could let me know what I need to do in order to ensure that this works where data does and does not exist as I cannot control if the live database will or will not have any existing data.

Thanks

Simon

Answer 1

You probably receive the error because you have orphaned records in the [rpt].[ReportLessonCompetency] table. An orphaned record is a record in a child table without a corresponding parent record in the parent table. In your case the [rpt].[ReportLessonCompetency] will contain values for [Grade] that don't exist in the [rpt].TraineeGrade(Id) table.

There are 2 options to get the foreign key created (though there's only 1 valid option imo).

Cleanup your data
First you could look up the records in the child table that don't have a corresponding parent record. Next, you should either delete/update those records in the child table or add the missing parent records to your parent table. Afterwards you'll be able to create the foreign key constraint. This is the best option by far since your referential integrety is guaranteed and your foreign key will be trusted.
You can find orphaned records by executing following query:

SELECT *
FROM [rpt].ReportLessonCompetency rlc
WHERE NOT EXISTS
(
    SELECT 1 
    FROM [rpt].TraineeGrade tg
    WHERE tg.Id = rlc.Grade
)

WITH NOCHECK
The other option would be to create the foreign key with WITH NOCKECK. SQL Server will create the foreign key without verifying the existing data in the table. When you update/insert data in the child table, those records will still be checked. Consequently, your foreign key will be marked as untrusted and the query optimizer won't consider your constraint to generate an execution plan.
Here you can find an example of how performance can be impacted.

Answer 2

You can avoid verifying FOREIGN KEY constraints against existing data by using WITH NOCHECK.

ALTER TABLE [rpt].ReportLessonCompetency WITH NOCHECK
ADD CONSTRAINT [FK_Grade_TraineeGrade_Id]
FOREIGN KEY (Grade) REFERENCES [rpt].TraineeGrade(Id)

I wouldn't recommend doing this as ignored constraint violations can cause an update to fail at a later point. You should clean up your data instead.