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

Question

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

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 the error:

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

I can fix this 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'd prefer to do it in one step, as each presumably needs a table scan and the table is tens of gigabytes in size.

Is it possible to get the ALTER TABLE to fix the error (by dropping rows) rather than exiting with an error?

Thanks.

Jon Seigel · Accepted Answer · 2011-01-27 16:07:34Z

4

Is it possible to get the ALTER TABLE to fix the error (by dropping rows) rather than exiting with an error?

The long and short - no. Do it in two steps as you are familiar with.

You could stop at

ALTER TABLE t1 CHECK CONSTRAINT fk

which turns it on for new records but leaves the existing data alone?

edited Jan 27, 2011 at 16:07

Jon Seigel

12.5k8 gold badges61 silver badges93 bronze badges

answered Jan 17, 2011 at 11:13

RichardTheKiwi

108k28 gold badges206 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nvogel Over a year ago

A word of warning regarding what cyberwiki said. In general do not use "ALTER TABLE t1 CHECK CONSTRAINT fk". Always use ALTER TABLE t1 WITH CHECK CHECK CONSTRAINT fk unless you plan to disable and reenable the constraint again straight away. The WITH CHECK makes a huge difference because without it the constraint will be marked as "not trusted". That means the optimizer will ignore it and some other features may be affected or disabled (some view updates may not work for example).

RichardTheKiwi Over a year ago

@dportas - if you follow the question, and the next statement "leaves existing data alone" you will see why WITH CHECK doesn't work

nvogel Over a year ago

@cyberwiki, sure. But the original request was to drop the rows that cause the errors. My understanding being that that Ben intends to correct the data before re-enabling the constraint, I just though it important to point out the disadvantage of re-enabling the constraint without WITH CHECK.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related