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

User · Answer

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

User · Answer

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

User · Answer

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 http   blogs msdn com b sqlserverstorageengine archive 2015 11 03 drop-if-exists-new-thing-in-sql-server-2016 aspx

User · Answer

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 dbo FK TableName TableName2      AND parent object id   OBJECT ID N dbo TableName       ALTER TABLE  dbo TableName  DROP CONSTRAINT  FK TableName TableName2

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 dbo FK TableName TableName2      AND parent object id   OBJECT ID N dbo TableName       ALTER TABLE  dbo TableName  DROP CONSTRAINT  FK TableName TableName2

User · Answer

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 http   blogs msdn com b sqlserverstorageengine archive 2015 11 03 drop-if-exists-new-thing-in-sql-server-2016 aspx

User · Answer

ALTER TABLE  dbo   TableName      DROP CONSTRAINT FK TableName TableName2

User · Answer

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

User · Answer

IF  OBJECT ID  DF Constraint   IS NOT NULL  BEGIN     ALTER TABLE  dbo   tableName      DROP CONSTRAINT DF Constraint END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 dbo FK TableName TableName2      AND parent object id   OBJECT ID N dbo TableName       ALTER TABLE  dbo TableName  DROP CONSTRAINT  FK TableName TableName2

User · Answer

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

User · Answer

ALTER TABLE  dbo   TableName      DROP CONSTRAINT FK TableName TableName2

User · Answer

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 dbo FK TableName TableName2      AND parent object id   OBJECT ID N dbo TableName       ALTER TABLE  dbo TableName  DROP CONSTRAINT  FK TableName TableName2

User · Answer

IF  OBJECT ID  DF Constraint   IS NOT NULL  BEGIN     ALTER TABLE  dbo   tableName      DROP CONSTRAINT DF Constraint END

User · Answer

ALTER TABLE  dbo   TableName      DROP CONSTRAINT FK TableName TableName2

User · Answer

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

User · Answer

ALTER TABLE  dbo   TableName      DROP CONSTRAINT FK TableName TableName2

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql