Fixing broken foreign keys with ALTER TABLE ... WITH CHECK CHECK CHECK

Question

I have a broken foreign key in SQL Server 2005. Here is the replay:

CREATE TABLE t2(i2 BIGINT NOT NULL PRIMARY KEY) CREATE TABLE t1(i1 BIGINT NOT NULL PRIMARY KEY) ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (i1) REFERENCES t2 (i2) ALTER TABLE t1 NOCHECK CONSTRAINT fk INSERT INTO t1 (i1) VALUES (0) 

If I subsequently run:

 ALTER TABLE t1 WITH CHECK CHECK CONSTRAINT fk 

I get an error message:

The ALTER TABLE statement contradicted the FOREIGN KEY "fk" constraint. The conflict occurred in the database "broken-fk", table "dbo.t2", column "i2".

I can fix it manually:

 DELETE FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.i2 = t1.i1) ALTER TABLE t1 WITH CHECK CHECK CONSTRAINT fk 

but I would prefer to do this in one step, since everyone apparently needs to scan the table, and the table is tens of gigabytes in size.

Is it possible to force ALTER TABLE to fix the error (by deleting the rows), and not exit with the error?

Thanks.

Answer 1

Is it possible to force ALTER TABLE to fix the error (by deleting the rows), and not exit with the error?

Long and short - no. Do this in two steps, as you know.

You can stay at

 ALTER TABLE t1 CHECK CONSTRAINT fk 

which includes it for new entries but leaves only existing data?