A way to check if foreign key exists in SQL 2005

Question

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.

score 80 · Accepted Answer · edited Oct 12 '12 at 10:52

80

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.

edited Oct 12 '12 at 10:52

Keith K

2,893
4
33
43

answered May 15 '09 at 14:38

CodeLikeBeaker

20,682
14
79
108

This script worked great! I can't believe you could script something like that from the menu. Thanks a bunch for the help. – May 15 '09 at 14:43
5

You can pretty much right click just about anything in SQL and have it generate a script for you, Tables, Stored Procedures, Foreign Key and more. Management studio is your friend once you learn some of the cool features it can do. – CodeLikeBeaker May 15 '09 at 14:47
This method only works if the default name of the foreign key is used. It's unreliable to work on names based on assumptions especially if you are working on databases created by others who might follow a different naming convention. Are there any ways to check if a particular column is a Foreign Key to another table instead of checking for Foreign Key's name? – Twisted Whisper Jan 09 '13 at 19:20
1

@TwistedWhisper - An answer to a similar question should point you the right way: http://stackoverflow.com/a/9090247/1075980 – Zarepheth Jun 14 '13 at 17:27

score 13 · Answer 2 · edited Dec 04 '13 at 10:09

13

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)

edited Dec 04 '13 at 10:09

SteveC

15,808
23
102
173

answered May 15 '09 at 14:39

ristonj

1,590
1
12
15

A way to check if foreign key exists in SQL 2005

2 Answers2

Linked