drop a foreign key constraint

Question

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

Answer 1

Joe,

If I understood your  question properly then I would like to suggest the way you looked for table exists or not, same way you can confirm that you FK exist or not by the following query.

SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'FK_TableName_TableName2') AND type = ('F')

---

Thanks
Manish

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

You can always use MS SQL Server Management Studio to get script for Drop or Create existence object:

The code will look like this:

IF  EXISTS (
	SELECT * FROM sys.foreign_keys 
	WHERE object_id = OBJECT_ID(N'[dbo].[FK_CONSTRAINTNAME]') 
	AND parent_object_id = OBJECT_ID(N'[dbo].[TABLENAME]')
)
	ALTER TABLE [dbo].[TABLENAME] 
		DROP CONSTRAINT [FK_CONSTRAINTNAME]
GO

Answer 3

Hi Joe,

Try:

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

---

Iric Wen
TechNet Community Support