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
Best Answer
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:
WITH NOCHECKThe 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.