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.

回答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]'))
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.

回答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)