How do I drop a foreign key constraint only if it exists in sql server?
SqlSql ServerSql Server-2005TsqlSql Problem Overview
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 Solutions
Solution 1 - Sql
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:
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
Solution 2 - Sql
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]
Solution 3 - Sql
In SQL Server 2016 you can use DROP IF EXISTS:
CREATE TABLE t(id int primary key,
parentid int
constraint tpartnt foreign key references t(id))
GO
ALTER TABLE t
DROP CONSTRAINT IF EXISTS tpartnt
GO
DROP TABLE IF EXISTS t
Solution 4 - Sql
IF (OBJECT_ID('DF_Constraint') IS NOT NULL)
BEGIN
ALTER TABLE [dbo].[tableName]
DROP CONSTRAINT DF_Constraint
END
Solution 5 - Sql
James's answer 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
Solution 6 - Sql
ALTER TABLE [dbo].[TableName]
DROP CONSTRAINT FK_TableName_TableName2
Solution 7 - Sql
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
Solution 8 - Sql
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.
Solution 9 - Sql
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
Solution 10 - Sql
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
Solution 11 - Sql
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
Solution 12 - Sql
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