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.

Joe Stefanelli · Accepted Answer · 2010-12-13 15:15:11Z

11

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;

answered Dec 13, 2010 at 15:15

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin B Over a year ago

So simple. Why did everyone else post such complicated replies?

Ian Nelson · Accepted Answer · 2010-12-13 15:16:59Z

1

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

answered Dec 13, 2010 at 15:16

Ian Nelson

59.1k20 gold badges79 silver badges104 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related