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])
go12 Answers
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
ENDIf 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 constraintYou 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.
5SQ = 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
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]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 tSee
2IF (OBJECT_ID('DF_Constraint') IS NOT NULL)
BEGIN ALTER TABLE [dbo].[tableName] DROP CONSTRAINT DF_Constraint
ENDJames'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)
endALTER TABLE [dbo].[TableName] DROP CONSTRAINT FK_TableName_TableName2Declare @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
ENDThe 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
ENDI 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 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_nameAll 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
