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

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

Declare @FKeyRemoveQuery NVarchar(max)

IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.TableName'))

BEGIN
SELECT @FKeyRemoveQuery='ALTER TABLE dbo.TableName DROP CONSTRAINT [' + LTRIM(RTRIM([name])) + ']'
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N'dbo.TableName')

EXECUTE Sp_executesql @FKeyRemoveQuery

END

IF (OBJECT_ID('DF_Constraint') IS NOT NULL)
BEGIN
ALTER TABLE [dbo].[tableName]
DROP CONSTRAINT DF_Constraint
END

I think this will helpful to you...

DECLARE @ConstraintName nvarchar(200)
SELECT
@ConstraintName = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE
KCU.TABLE_NAME = 'TABLE_NAME' AND
KCU.COLUMN_NAME = 'TABLE_COLUMN_NAME'
IF @ConstraintName IS NOT NULL EXEC('alter table TABLE_NAME drop CONSTRAINT ' + @ConstraintName)

It will delete foreign Key Constraint based on specific table and column.

You can use those queries to find all FKs for your table.

Declare @SchemaName VarChar(200) = 'Schema Name'
Declare @TableName VarChar(200) = 'Table name'

-- Find FK in This table.
SELECT
'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +

'ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.parent_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName


-- Find the FKs in the tables in which this table is used
SELECT
' IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +

' ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.referenced_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName

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

This is a lot simpler than the current proposed solution:

IF (OBJECT_ID('dbo.FK_ConstraintName', 'F') IS NOT NULL)
BEGIN
ALTER TABLE dbo.TableName DROP CONSTRAINT FK_ConstraintName
END

If you need to drop another type of constraint, these are the applicable codes to pass into the OBJECT_ID() function in the second parameter position:

C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
UQ = UNIQUE constraint

You can also use OBJECT_ID without the second parameter.


Full List of types [here][1]:

Object type:

> AF = Aggregate function (CLR)
> C = CHECK constraint
> D = DEFAULT (constraint or stand-alone)
> F = FOREIGN KEY constraint
> FN = SQL scalar function
> FS = Assembly (CLR) scalar-function
> FT = Assembly (CLR) table-valued function
> IF = SQL inline table-valued function
> IT = Internal table
> P = SQL Stored Procedure
> PC = Assembly (CLR) stored-procedure
> PG = Plan guide
> PK = PRIMARY KEY constraint
> R = Rule (old-style, stand-alone)
> RF = Replication-filter-procedure
> S = System base table
> SN = Synonym
> SO = Sequence object

**Applies to: SQL Server 2012 through SQL Server 2014.**

> SQ = Service queue
> TA = Assembly (CLR) DML trigger
> TF = SQL table-valued-function
> TR = SQL DML trigger
> TT = Table type
> U = Table (user-defined)
> UQ = UNIQUE constraint
> V = View
> X = Extended stored procedure


[1]:

[To see links please register here]

[James's answer][1] works just fine if you know the name of the actual constraint. The tricky thing is that in legacy and other real world scenarios you may not **know** what the constraint is called.

If this is the case you risk creating duplicate constraints, to avoid you can use:


create function fnGetForeignKeyName
(
@ParentTableName nvarchar(255),
@ParentColumnName nvarchar(255),
@ReferencedTableName nvarchar(255),
@ReferencedColumnName nvarchar(255)
)
returns nvarchar(255)
as
begin
declare @name nvarchar(255)

select @name = fk.name from sys.foreign_key_columns fc
join sys.columns pc on pc.column_id = parent_column_id and parent_object_id = pc.object_id
join sys.columns rc on rc.column_id = referenced_column_id and referenced_object_id = rc.object_id
join sys.objects po on po.object_id = pc.object_id
join sys.objects ro on ro.object_id = rc.object_id
join sys.foreign_keys fk on fk.object_id = fc.constraint_object_id
where
po.object_id = object_id(@ParentTableName) and
ro.object_id = object_id(@ReferencedTableName) and
pc.name = @ParentColumnName and
rc.name = @ReferencedColumnName

return @name
end

go

declare @name nvarchar(255)
declare @sql nvarchar(4000)
-- hunt for the constraint name on 'Badges.BadgeReasonTypeId' table refs the 'BadgeReasonTypes.Id'
select @name = dbo.fnGetForeignKeyName('dbo.Badges', 'BadgeReasonTypeId', 'dbo.BadgeReasonTypes', 'Id')
-- if we find it, the name will not be null
if @name is not null
begin
set @sql = 'alter table Badges drop constraint ' + replace(@name,']', ']]')
exec (@sql)
end


[1]:

[To see links please register here]

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

The more simple solution is provided in [Eric Isaacs's][1] 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]


[1]:

[To see links please register here]