How to Drop a Foreign Key Constraint Only If It Exists in SQL Server

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

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]

A way to check if foreign key exists in SQL 2005

You can use this script:

IF EXISTS (SELECT * 
           FROM sys.foreign_keys 
           WHERE object_id = OBJECT_ID(N'[dbo].[FK_NAME]') 
             AND parent_object_id = OBJECT_ID(N'[dbo].[MyTable]'))
BEGIN
    -- do stuff
END

This can be done if you expand out the table and right click on an existing FK and choose script key as "DROP TO" and then you will get a generated script from SQL.

How can I drop a table if there is a foreign key constraint in SQL Server?

You must drop the constraint before you can drop the table. Otherwise its rule violation that could break the databases Referential Integrity.

How to get foreign key relationships see this old question.
SQL DROP TABLE foreign key constraint

Using the SQL Server 2016 syntax how to use drop constraint 'if exists' when the table itself may or may not exist?

From SQL Server 2016 onwards, you have DROP IF EXISTS syntax. First check the existance of the table, before going ahead with dropping of constraint, Table.

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('<TableName>'))
BEGIN
    ALTER TABLE <TableName> DROP CONSTRAINT IF EXISTS <CONSTRAINTNAME>;
    DROP TABLE IF EXISTS <TableName>;
END
GO

Also, if you are planning to drop the table, you dont need to drop the constraint. You can simply drop the table directly. Below would suffice.

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('<TableName>'))
BEGIN
    DROP TABLE IF EXISTS <TableName>;
END
GO

But, I would suggest you to check and drop the foreign keys associated with this table and then drop this table. Otherwise you will get error. You can check the foreign keys associated with the table from the below script. First you need to drop them, before dropping the tables.

EXEC sp_fkeys [ @pktable_name = ] 'pktable_name'   

If foreign key exists, drop it

If you are using SQL Server, this should works

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FKName]') AND parent_object_id = OBJECT_ID('TableName'))
  alter table TableName drop constraint FKName

How can I ensure a foreign key constraint doesn't exist, if I don't know whether the table exists?

As far as your question is concerned, and as commented by Dale K, you can't do that in a single statement.

Instead, one option is to first check catalog table information_schema.referential_constraints for the existence of the constraint before attempting to drop it, like:

if (exists (
    select 1
    from information_schema.referential_constraints 
    where constraint_name  = 'myconstraint'
))
begin
    alter table mytable drop constraint myconstraint;
end

If the table does not exist, then the if condition will not be satisfied, and the alter table statement will not run.

Note that you might want to add a filter on the constraint_schema column of referential_constraints (since constraints of the same name may exist in different schemas).

How to remove foreign key constraint in sql server?

Try following

ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <FOREIGN_KEY_NAME>

Refer : http://www.w3schools.com/sql/sql_foreignkey.asp

---

Related Topics