You can use the NOT VALID modifier when creating a foreign key to stop it verifying existing data. For instance:
ALTER TABLE sales
ADD CONSTRAINT sales_date_fk FOREIGN KEY (sale_date)
REFERENCES dates (date_idx) NOT VALID;
I would not recommend this though, unless something prevents you from populating the new child table so you have no choice.
This is ...
This might happen due to the the value physically written on the page being incorrect (data corruption). Or a bug in the product (probably less likely but possible). Run DBCC CHECKDB to see if that finds any issues. – Martin Smith Jun 18 '16 at 0:07
Along Martin's comment - did the data get into the table via MERGE? Here is one bug (closed ...
ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [CHK_TOTALS] enables the constraint for all future updates to the table. Since you originally created that constraint WITH NOCHECK, existing at that time rows were not verified, and this applies (emphasis mine):
WITH CHECK | WITH NOCHECK
Specifies whether the data in the table is or isn't validated against a newly ...