CODEWITHSUNDEEP
sqlsql-server-2005foreign-keys
Barbara Jackson
Barbara Jackson

Reputation:

A way to check if foreign key exists in SQL 2005

Is there an easy way to check if a foreign key exists for a column in a table? I am writing a script which will add the foreign key only if it does not exist.

Upvotes: 48

Views: 57068

Answers (2)

ristonj
ristonj

Reputation: 1608

Woo-hoo! I just spent the past two days doing this.

IF NOT EXISTS ( SELECT  name
                FROM    sys.foreign_keys
                WHERE   name = 'FK_Name' ) 
    ALTER TABLE table_name ADD CONSTRAINT FK_Name FOREIGN KEY (idcol) 
                           REFERENCES OtherTable(idcol)

Upvotes: 13

CodeLikeBeaker
CodeLikeBeaker

Reputation: 21312

You can use this script:

IF EXISTS (SELECT * 
           FROM sys.foreign_keys 
           WHERE object_id = OBJECT_ID(N'[dbo].[FK_NAME]') 
             AND parent_object_id = OBJECT_ID(N'[dbo].[MyTable]'))
BEGIN
    -- do stuff
END

This can be done if you expand out the table and right click on an existing FK and choose script key as "DROP TO" and then you will get a generated script from SQL.

Upvotes: 80

Related Questions