How to Drop All Foreign Key Constraints in All Tables

How to drop all foreign keys from a SQL Server database?

There is a table named INFORMATION_SCHEMA.TABLE_CONSTRAINTS which stores all tables constraints. constraint type of FOREIGN KEY is also keeps in that table. So by filtering of this type you can reach to all foreign keys.

SELECT  *
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'

If you create a dynamic query (for DROP-ing the foreign key) in order to alter the table, you can reach to the aim of altering the constraints of all tables.

WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME IN ('Table1', 'Table2') AND CONSTRAINT_NAME LIKE '%FK__%__DL%'))
BEGIN
    DECLARE @sql_alterTable_fk NVARCHAR(2000)

    SELECT  TOP 1 @sql_alterTable_fk = ('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'
            AND TABLE_NAME IN ('Table1', 'Table2')
            AND CONSTRAINT_NAME LIKE '%FK__%__DL%'

    EXEC (@sql_alterTable_fk)
END

EXISTS function with its parameter assures that there is at least one constrain for foreign key.

How to drop all FK contraints referencing a table and add them back?

I found the answer I need to disable all foreign key constraints first then delete the data like paparazzo said then re-enable them.

Here are the scripts:

Disable Foreign Key Constraints.

DECLARE @sql NVARCHAR(MAX) = N'';   

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;

Enable them back

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;

MySQL DROP all tables, ignoring foreign keys

I found the generated set of drop statements useful, and recommend these tweaks:

1. Limit the generated drops to your database like this:

SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';

Note 1: This does not execute the DROP statements, it just gives you a list of them. You will need to cut and paste the output into your SQL engine to execute them.

Note 2: If you have VIEWs, you'll have to correct each DROP TABLE `VIEW_NAME` statement to DROP VIEW `VIEW_NAME` manually.

2. Note, per http://dev.mysql.com/doc/refman/5.5/en/drop-table.html, dropping with cascade is pointless / misleading:

"RESTRICT and CASCADE are permitted to make porting easier. In MySQL 5.5, they do nothing."

Therefore, in order for the drop statements to work if you need:

SET FOREIGN_KEY_CHECKS = 0

This will disable referential integrity checks - so when you are done performing the drops you need, you will want to reset key checking with

SET FOREIGN_KEY_CHECKS = 1

3. The final execution should look like:

SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;

NB: to use output of SELECT easier, mysql -B option can help.

Drop all foreign key constrain on a table in Snowflake

This isn't possible within a single SQL statement. Snowflake just guarantees that "dropping a unique/primary key with foreign key references drops all the referencing foreign keys together with the unique/primary key"

See here: https://docs.snowflake.com/en/sql-reference/constraints-drop.html

One solution for dropping more constraints may be creating a stored procedure that loops over all constraints and deletes them.

More information about creating stored procedures: https://docs.snowflake.com/en/sql-reference/stored-procedures.html

The information schema-view where you get all constraints from: https://docs.snowflake.com/en/sql-reference/info-schema/table_constraints.html

Is it possible to drop all foreign key constraints on a table at once in mySQL 5?

You can surely select * the table to a temp table, drop and recreate it, then copy back.

SQL DROP TABLE foreign key constraint

No, this will not drop your table if there are indeed foreign keys referencing it.

To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:

SELECT 
    'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '].[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

---

Related Topics