How do I drop a foreign key constraint only if it exists in sql server?

john_2041 0
Viewed 343886

I can drop a table if it exists using the following code but do not know how to do the same with a constraint:

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = (N'U')) DROP TABLE TableName
go

I also add the constraint using this code:

ALTER TABLE [dbo].[TableName] 
  WITH CHECK ADD CONSTRAINT [FK_TableName_TableName2] FOREIGN KEY([FK_Name])
    REFERENCES [dbo].[TableName2] ([ID])
go
sql-server-2005 tsql sql-server sql
12 Answers

The more simple solution is provided in Eric Isaacs's answer. However, it will find constraints on any table. If you want to target a foreign key constraint on a specific table, use this:

IF EXISTS (SELECT * 
  FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N'FK_TableName_TableName2')
   AND parent_object_id = OBJECT_ID(N'dbo.TableName')
)
  ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [FK_TableName_TableName2]
user_27000 
ALTER TABLE [dbo].[TableName]
    DROP CONSTRAINT FK_TableName_TableName2
user_160760

The accepted answer on this question doesn't seem to work for me. I achieved the same thing with a slightly different method:

IF (select object_id from sys.foreign_keys where [name] = 'FK_TableName_TableName2') IS NOT NULL
BEGIN
    ALTER TABLE dbo.TableName DROP CONSTRAINT FK_TableName_TableName2
END
user_18781410

Ok, I know I'm late to the party, but here is the syntax I think is best. Add a schema name if needed to the OBJECT_ID clause.

IF OBJECTPROPERTY(OBJECT_ID(N'My_FK_name'),'IsConstraint') =1
ALTER TABLE dbo.TableName DROP CONSTRAINT My_FK_name
user_58681580

All table constraints will be stored in INFORMATION_SCHEMA.TABLE_CONSTRAINTS

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C WHERE C.CONSTRAINT_NAME = '<CONSTRAINT NAME>' AND C.TABLE_NAME = '<TABLE NAME>')
BEGIN
    ALTER TABLE <TABLE NAME>
        DROP CONSTRAINT <CONSTRAINT NAME>
END
GO
user_7962810
Related