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 "TMSCentral_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.