Checking for a foreign key before adding it with a sql script

Question

I have a SQL script which adds a foreign key to a table, as shown below.

ALTER TABLE [security].[Pages] WITH NOCHECK
ADD CONSTRAINT [FK_Pages_PageClasses] FOREIGN KEY ([PageClassId]) REFERENCES [security].[PageClasses]([PageClassId]) ON DELETE NO ACTION ON UPDATE NO ACTION;

Sometimes the table already has this foreign key so an error is shown when it is run from the Management Studio. Is it possible to add a query to check if the key exists before applying it? Is there a better way to do this? This must work for MS SQL 2005 and 2008.

Answer 1

Use the OBJECT_ID function to test for existence.

IF OBJECT_ID('[security].[FK_Pages_PageClasses]') IS NULL
    ALTER TABLE [security].[Pages] WITH NOCHECK
        ADD CONSTRAINT [FK_Pages_PageClasses] FOREIGN KEY ([PageClassId]) REFERENCES [security].[PageClasses]([PageClassId]) ON DELETE NO ACTION ON UPDATE NO ACTION;

Answer 2

You can check for the existence of the foreign key by looking in the sys.foreign_keys object catalog view:

IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id = object_id(N'[Security].[FK_Pages_PageClasses]') and parent_object_id = object_id(N'[Security].[Pages]'))
BEGIN
    ALTER TABLE [security].[Pages] WITH NOCHECK
    ADD CONSTRAINT [FK_Pages_PageClasses] FOREIGN KEY ([PageClassId]) REFERENCES [security].[PageClasses]([PageClassId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
END