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

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

Nervy

Answer 1

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]

Volsci

its the if exists bit i am really after.. sorry. i'll update my question so it's more clear! – Nervy

If you are using EF generated Foreign Keys with dots in the name you need to put brackets around the names like this [dbo].[FK_dbo.MyTable_Etc] – Amphibiotic

In MSSQL 2017, it looks like the column is now call constraint_object_id instead of just object_id – Achromatism

This doesn't work. OBJECT_ID('[CONSTRAINT_NAME]', 'F') on a foreign key that I know exists, and it returned null. – Reservation

I ended up adding a different solution to this question but achieves the same result. – Reservation

@MelbourneDeveloper You might need the schema prefix, if the foreign key exists in a non-dbo schema. For example, IF (SELECT OBJECT_ID(N'[Schema].[FK_Name]', N'F')) IS NOT NULL . I had similar issues to you (your solution worked for me, as well), then I got it working via this example. I'm running SQL Server 2012. – Jeanejeanelle

@JamesL I had to rollback the edits in which you changed your answer to the one provided by Eric Isaacs without credit. Your solution is equally valid and should not go lost. – Washrag

From SQL2016 onwards you can just do ALTER TABLE [dbo.TableName] DROP CONSTRAINT IF EXISTS [FK_TableName_TableName2] – Anamorphism

I end up with writing select statement inside if exists as below, because object_id(fkname) not working SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_dbo.tablename_dbo_fk_name' AND parent_object_id = OBJECT_ID(N'dbo.tablename') – Matthias

Answer 2

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

Limekiln

Full list of types here (that is, this works for all sorts of stuff, not just keys). – Muriel

Took the liberty of adding link and list of types. – Orchardist

It appears that if the constraint is not in the dbo schema then you also need to include the schema name. E.g: OBJECT_ID('MySchema.FK_MyConstraint', 'F') – Maltase

This way may be simpler, but the other way is better for explicitly finding and removing constraints, even constraints with the same name applied to different tables/schemas/databases. – Mariandi

I see one issue here it never make sure if the contain is on the table where we are dropping contain. – Whitehouse

Answer 3

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

Embowed

This syntax is also supported by ssms when you configure the check for object existence scripting option in case you want to get the proper script straight out of ssms. – Tymes

This only works if you know for sure the table itself does really exists. Better to check that too. – Laborious

Answer 4

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

Bijugate

Answer 5

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

Tb

Answer 6

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

Tribesman

only thing extra I would add is include the name as a filter in the select from sys.foreign_keys as there could be multiple foreign keys on the table – Palomino

Answer 7

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

Reservation

Answer 8

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.

Windflower

Answer 9

This worked smoothly for Azure Sql server:

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

Leaseback

Answer 10

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 

Lizzielizzy

Answer 11

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

Kanazawa

Answer 12

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

Thun