How to Drop All Foreign-Key Constraints on a Table in SQL Server 2000

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.

Temporarily disable all foreign key constraints

To 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 + N' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sys.sp_executesql @sql;

To re-enable:

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 + N' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sys.sp_executesql @sql;

However, you will not be able to truncate the tables, you will have to delete from them in the right order. If you need to truncate them, you need to drop the constraints entirely, and re-create them. This is simple to do if your foreign key constraints are all simple, single-column constraints, but definitely more complex if there are multiple columns involved.

Here is something you can try. In order to make this a part of your SSIS package you'll need a place to store the FK definitions while the SSIS package runs (you won't be able to do this all in one script). So in some utility database, create a table:

CREATE TABLE dbo.PostCommand(cmd nvarchar(max));

Then in your database, you can have a stored procedure that does this:

DELETE other_database.dbo.PostCommand;

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'ALTER TABLE ' 
   + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
   + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
   + ' ADD CONSTRAINT ' + fk.name + ' FOREIGN KEY (' 
   + STUFF((SELECT ',' + c.name
    FROM sys.columns AS c 
        INNER JOIN sys.foreign_key_columns AS fkc 
        ON fkc.parent_column_id = c.column_id
        AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', 'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + 
QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id))
+ '(' + 
STUFF((SELECT ',' + c.name
    FROM sys.columns AS c 
        INNER JOIN sys.foreign_key_columns AS fkc 
        ON fkc.referenced_column_id = c.column_id
        AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'') + ');
' FROM sys.foreign_keys AS fk
WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0;

INSERT other_database.dbo.PostCommand(cmd) SELECT @sql;

IF @@ROWCOUNT = 1
BEGIN
  SET @sql = N'';

  SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
    + ' DROP CONSTRAINT ' + fk.name + ';
  ' FROM sys.foreign_keys AS fk;

  EXEC sys.sp_executesql @sql;
END

Now when your SSIS package is finished, it should call a different stored procedure, which does:

DECLARE @sql nvarchar(max);

SELECT @sql = cmd FROM other_database.dbo.PostCommand;

EXEC sys.sp_executesql @sql;

If you're doing all of this just for the sake of being able to truncate instead of delete, I suggest just taking the hit and running a delete. Maybe use bulk-logged recovery model to minimize the impact of the log. In general I don't see how this solution will be all that much faster than just using a delete in the right order.

In 2014 I published a more elaborate post about this here:

• Drop and Re-Create All Foreign Key Constraints in SQL Server

How to clear data from tables with foreign key contraints in SQL Azure

Nathan,

There are a couple of things I should point out...

1) The example code you posted is dropping and creating a DEFAULT CONSTRAINT (i.e. something that sets the default value for a column if you do not specify a value during an insert). A default constraint has no affect on the ability to truncate a table (as you rightly point out, a table that has Foreign Key constraints cannot be truncated).

2) I think sp_fkeys was for SQL 2000 and therefore may not work anymore in later versions of SQL Server (even though it still exists).

Below I have attached a script I use to identify all Foreign Key constraints on a table, and generate Create, Drop and Check statements. Although I haven't ever used it on SQL Azure, I have used it many times in a Production enivronment for SQL Server 2008 R2.

I hope it helps. Let me know if you have any questions.

Ash

CREATE FUNCTION [utils].[uf_ForeignKeyScripts]
(
@PrimaryKeyTable varchar(128), @PrimaryKeyTableSchema varchar(32)
)
RETURNS @Scripts TABLE 
    (
    ForeignKeyName          varchar(128)
,   IfExistsStatement       varchar(1000)
,   DropStatement           varchar(1000)
,   IfNotExistsStatement    varchar(1000)
,   CreateStatement         varchar(1000)
,   CheckStatement          varchar(1000)
,   NoCheckStatement        varchar(1000)
    )
    AS

/*
This function returns statements used to create, drop, and check all Foreign Key constraints that reference a given table.
These statements can be then added to T-SQL scripts.

Example usage (ensure selection of the Results to Text option in SSMS) :

1) To create statements to check all foreign keys

SELECT
    IfExistsStatement + CHAR(13) +
    CHAR(9) + CheckStatement + CHAR(13)
FROM
    utils.uf_ForeignKeyScripts('t_Dim_Date','dbo')      
    ;

This will return a formatted statement to check the existence of a foreign key and if it exists, check that data does not violate the key.

*/

BEGIN
    INSERT INTO
        @Scripts
        (
        ForeignKeyName
    ,   IfExistsStatement
    ,   DropStatement
    ,   IfNotExistsStatement
    ,   CreateStatement
    ,   CheckStatement
    ,   NoCheckStatement
        )
    SELECT
        FK.name AS ForeignKeyName
    ,   'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' + SFK.name + '.' + FK.name + ''') ' + 
        'AND parent_object_id = OBJECT_ID(N''' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + '''))'
        AS IfExistsStatement
    ,   'ALTER TABLE ' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + ' ' + 
        'DROP CONSTRAINT ' + FK.name + CHAR(13) + ';' 
        AS DropStatement
    ,   'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' + SFK.name + '.' + FK.name + ''') ' + 
        'AND parent_object_id = OBJECT_ID(N''' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + '''))'
        AS IfNotExistsStatement 
    ,   'ALTER TABLE ' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + ' ' +
        'WITH CHECK ADD CONSTRAINT ' + FK.name + ' ' + 
        'FOREIGN KEY (' + C.FKColumns + ') ' + 
        'REFERENCES ' + ST.name + '.' + OBJECT_NAME(fk.referenced_object_id) + ' ' + 
        '(' + C.FKColumns + ')' + CHAR(13) + ';' 
        AS CreateStatement
    ,   'ALTER TABLE ' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + ' ' +
        'CHECK CONSTRAINT ' + FK.name + CHAR(13) + ';'
        AS CheckStatement
    ,   'ALTER TABLE ' + SFK.name + '.' + OBJECT_NAME(FK.parent_object_id) + ' ' +
        'NOCHECK CONSTRAINT ' + FK.name +   CHAR(13) + ';'
        AS NoCheckStatement
    FROM
        sys.foreign_keys AS FK
    INNER JOIN
        sys.schemas AS SFK -- schema of foreign key table
    ON
        FK.schema_id = SFK.schema_id
    INNER JOIN
        sys.tables AS T -- primary key table
    ON
        FK.referenced_object_id = T.object_id
    INNER JOIN
        sys.schemas AS ST -- schema of primary key table
    ON
        T.schema_id = ST.schema_id
    CROSS APPLY
        (
        /* Get all columns to handle composite keys */
        SELECT 
            SFKC.constraint_object_id
        ,   utils.uf_ConcatanateStringWithDelimiter(COL_NAME(SFKC.referenced_object_id, SFKC.referenced_column_id),', ') AS FKColumns
        FROM
            sys.foreign_key_columns AS SFKC
        WHERE
            SFKC.constraint_object_id = FK.object_id
        GROUP BY
            SFKC.constraint_object_id
        )
        AS C
    WHERE
        OBJECT_NAME(T.object_id) = @PrimaryKeyTable
    AND ST.name = @PrimaryKeyTableSchema
    ;
    RETURN 
END

How to drop all tables in a SQL Server database?

It doesn't work for me either when there are multiple foreign key tables.

I found that code that works and does everything you try (delete all tables from your database):

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' +  tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSforeachtable 'DROP TABLE ?'
GO

You can find the post here. It is the post by Groker.

Turn off constraints temporarily (MS SQL)

You can disable FK and CHECK constraints only in SQL 2005+. See ALTER TABLE

ALTER TABLE foo NOCHECK CONSTRAINT ALL

or

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

Primary keys and unique constraints can not be disabled, but this should be OK if I've understood you correctly.

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Correct; you cannot truncate a table which has an FK constraint on it.

Typically my process for this is:

1. Drop the constraints
2. Trunc the table
3. Recreate the constraints.

(All in a transaction, of course.)

Of course, this only applies if the child has already been truncated. Otherwise I go a different route, dependent entirely on what my data looks like. (Too many variables to get into here.)

The original poster determined WHY this is the case; see this answer for more details.

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.

---

Related Topics