What to do when the alter table statement conflicted with the foreign key constraint

1.1K Asked by alexDuncan in SQL Server , Asked on Oct 3, 2022

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 grateful 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.

Answered by AI

If the alter table statement conflicted with the foreign key constraint -

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.

What to do when the alter table statement conflicted with the foreign key constraint

Your Answer