How to Drop a SQL foreign constraint by column names

Question

We have many DB's that are identical schema, but the FK names might not be. We do a lot of cloning for testing and such.

Ran into a problem with the clone when we added a self referencing Foreign Key. We can't go back and change all the older DB's, but just need a way to clone and skip past the constraint, and re apply at the end.

// Keeping the columns simplified to just the 2 main ones causing the issue.

Table [Employee]

Columns [EmpId(pk), ManagerId(fk)]

ManagerId --> EmpId

So, if the clone trys to insert the Employee wiht a managerId that does not exist, you get an FK error.

Found several ways to Alter(drop then add) or rename the FK, but i need to know at cone application run time the FK's name, so i can alter.

Please no lectures on cloning, or copying, or why we're doing things wrong. We did multi DB's this way and self referencing for internal reasons.

Or just disable all the contraints. http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql — Yogurt The Wise, Jan 03 '14 at 18:28

score 1 · Answer 1 · edited May 23 '17 at 12:03

What i came up with.

I can get the FK NAME and then ALTER the constraint, do my operations, then reapply it.

DECLARE @fkname VARCHAR(100)
DECLARE @Command_drop NVARCHAR(1000)
DECLARE @Command_add NVARCHAR(1000)

    --GET THE FK NAME by column Names
    -- thanks to marc_s https://stackoverflow.com/a/3907957/295734
SELECT  @fkname = fk.name
FROM    sys.foreign_keys fk
        INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        INNER JOIN sys.columns c1 ON fkc.parent_column_id = c1.column_id
                                     AND fkc.parent_object_id = c1.object_id
        INNER JOIN sys.columns c2 ON fkc.referenced_column_id = c2.column_id
                                     AND fkc.referenced_object_id = c2.object_id
WHERE   fkc.parent_object_id = OBJECT_ID(N'dbo.employee')
        AND c1.name = 'manager_employee_id'
        AND c2.name = 'id'

SET @Command_drop = 'ALTER TABLE dbo.Employee DROP CONSTRAINT ' + @fkname
SET @Command_add = 'ALTER TABLE dbo.Employee ADD CONSTRAINT ' + @fkname
    + ' FOREIGN KEY (manager_employee_id) REFERENCES Employee(id)'
    -- DROP the constraint
EXEC (@Command_drop)
    -- DO SOME OPERATION HERE --
    -- Re ADD the constraint
EXEC (@Command_add)

OR

Disable All Foreign Keys

OR

change alter to - untested

disable:

ALTER TABLE <yourtable_Name> NOCHECK CONSTRAINT <yourconstraint_Name>

enable:

ALTER TABLE <yourtable_Name> CHECK CONSTRAINT <yourconstraint_Name>

This also keeps the FK the same name, so anything else referencing it, will not have to be changed. — Yogurt The Wise, Jan 03 '14 at 17:10

How to Drop a SQL foreign constraint by column names

1 Answers1