SQL Server: How do I add a constraint to an existing table but only if the constraint does not already exist?

Question

I need to add a constraint to an existing SQL server table but only if it does not already exist.

I am creating the constraint using the following SQL.

ALTER TABLE [Foo] ADD CONSTRAINT [FK_Foo_Bar] FOREIGN KEY ([BarId]) REFERENCES [Bar] ([BarId]) ON UPDATE CASCADE ON DELETE CASCADE

I'm hoping I can add some SQL to the begining of the SQL to test for the existence of the constraint but I have no idea how.

Answer 1

Personally I would drop the existing constraint, and recreate it - in case the one that is there is in some way different

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[MyFKName]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE dbo.MyTableName DROP CONSTRAINT MyFKName
GO
ALTER TABLE dbo.MyTableName ADD CONSTRAINT [MyFKName] ...

Answer 2

I'd recommend using the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view. It's portable across different database engines:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='Foo' AND CONSTRAINT_NAME='FK_Foo_Bar' AND CONSTRAINT_TYPE='FOREIGN KEY'