Adding constraint conflicts with itself, even though it doesn't exist yet

Question

I'm adding delete cascading to a Table. The Clone table has a column DeviceID that is a foreign key to the Device table's DeviceID column. So the SQL script drops the original FK constraint, and attempts to add the new one:

IF EXISTS
(
    SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME = 'FK_Clone_Device'
)
BEGIN
    ALTER TABLE Clone
    DROP CONSTRAINT FK_Clone_Device
END

IF NOT EXISTS
(
    SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME = 'FK_Clone_Device_Cascade'
)
BEGIN
    ALTER TABLE Clone
    ADD CONSTRAINT FK_Clone_Device_Cascade
    FOREIGN KEY (DeviceID) REFERENCES Device(DeviceID) ON DELETE CASCADE
END

When I run this script, I get the following error:

 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Clone_Device_Cascade". The conflict occurred in database "DevelopmentDB", table "dbo.Device", column 'DeviceID'.

Maybe I'm misunderstanding the error message, but it sounds like it's conflicting with itself. I'm confused that it happened on the Device table though.

There is an index in the Clone table on DeviceID. Would that matter?

This is on SQL SERVER R2 (Azure)

Answer 1

Sounds like you currently have data in the table that would violate the FK you are trying to create. One way to test this is to add "WITH (NOCHECK)" to the ALTER TABLE statement and see if it lets you create the constraint.

If it does let you create the constraint with NOCHECK, you can either leave it that way and the constraint will only be used to test future inserts/updates, or you can investigate your data to fix the FK violations.

So your example would be:

ALTER TABLE Clone WITH NOCHECK
ADD CONSTRAINT FK_Clone_Device_Cascade
FOREIGN KEY (DeviceID) REFERENCES Device(DeviceID) ON DELETE CASCADE