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.

Thanks

Answer 1

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]'))

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.

Hope this helps.

Answer 2

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)