To delete a foreign key constraint In Object Explorer, expand the table with the constraint and then expand Keys. Right-click the constraint and then click Delete.
To drop a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student ) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint. In our example, the name of this constraint is fk_student_city_id .
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
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 t
See https://web.archive.org/web/20151105064708/http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016.aspx
IF (OBJECT_ID('DF_Constraint') IS NOT NULL)
BEGIN
ALTER TABLE [dbo].[tableName]
DROP CONSTRAINT DF_Constraint
END
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With