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 already 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] ...

The current, more modern, code I am using is:

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MyFKName]') AND parent_object_id = OBJECT_ID(N'[dbo].[MyTableName]'))
    ALTER TABLE dbo.[MyTableName] DROP CONSTRAINT [MyFKName]
GO
ALTER TABLE dbo.[MyTableName] ADD CONSTRAINT [MyFKName] FOREIGN KEY ...

not sure if there is any advantage of checking sys.objects ... or sys.foreign_keys ... but at some point I decided on sys.foreign_keys

Starting with SQL2016 new "IF EXISTS" syntax was added which is a lot more readable:

-- For SQL2016 onwards:
ALTER TABLE dbo.[MyTableName] DROP CONSTRAINT IF EXISTS [MyFKName]
GO
ALTER TABLE dbo.[MyTableName] ADD CONSTRAINT [MyFKName] FOREIGN KEY ...

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'