CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
Retrocoder
Retrocoder

Reputation: 4703

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

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.

Upvotes: 2

Views: 5821

Answers (2)

Ian Nelson
Ian Nelson

Reputation: 58703

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

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;

Upvotes: 11

Related Questions