Sql-server – way to truncate table that has foreign a key

ddlforeign keysql serversql-server-2008truncate

Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?

Best Answer

No, you either need to delete and re-create the keys, or wait for the delete and re-seed. Disabling the foreign key temporarily might make the delete faster, but it still won't allow a truncate.

ALTER TABLE [dbo].[tablename] NOCHECK CONSTRAINT ALL;

-- delete, reseed, etc.

ALTER TABLE [dbo].[tablename] WITH CHECK CHECK CONSTRAINT ALL;

Very easy to automate this by building dynamic SQL from the metadata tables, depending on exactly which table(s) you need to target. The above is just a sample to demonstrate how it is done for a single table. For example, this will do so for each table that is the target of a foreign key and has an IDENTITY column:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SET NOCOUNT ON;';

;WITH s(t) AS
 (
   SELECT 
     QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id)) 
     + '.' + QUOTENAME(OBJECT_NAME(referenced_object_id))
  FROM sys.foreign_keys AS k
  WHERE EXISTS 
  (
    SELECT 1 FROM sys.identity_columns 
    WHERE [object_id] = k.referenced_object_id
  )
  GROUP BY referenced_object_id
)
SELECT @sql = @sql + N'
  ALTER TABLE ' + t + ' NOCHECK CONSTRAINT ALL;
  DELETE ' + t + ';
  DBCC CHECKIDENT(''' + t + ''', RESEED, 0) WITH NO_INFOMSGS;
  ALTER TABLE ' + t + 'WITH CHECK CHECK CONSTRAINT ALL;'
FROM s;

PRINT @sql;
-- EXEC sp_executesql @sql;

It may be that the output gets truncated, but this is just a limitation of PRINT (8K) - the actual command is complete.

PS this assumes SQL Server 2005 or better. Always useful to specify your version as part of your question (usually with a tag).

Related Solutions

Sql-server – View foreign key constraints so I can delete a table

And here's how to generate the script @Shark showed for all the tables you want to drop. Let's say you have the following tables:

USE tempdb;
GO

CREATE TABLE dbo.z(z INT PRIMARY KEY); -- we won't delete this one

CREATE TABLE dbo.a
(
 a INT PRIMARY KEY FOREIGN KEY REFERENCES dbo.z(z)
);

CREATE TABLE dbo.b
(
 b INT PRIMARY KEY, 
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

CREATE TABLE dbo.c
(
 c INT PRIMARY KEY, 
 b INT FOREIGN KEY REFERENCES dbo.b(b), 
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

-- we won't drop this table either, but we'll need to drop
-- the constraint:

CREATE TABLE dbo.d
(
 d INT, 
 c INT FOREIGN KEY REFERENCES dbo.c(c)
);

But we only want to delete a, b, and c.

-- load the tables you want to delete into a table variable:

DECLARE @tables_to_delete TABLE (t NVARCHAR(512));

INSERT @tables_to_delete VALUES('dbo.a'),('dbo.b'),('dbo.c');


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

-- build a list of the foreign keys you'll have to drop first:

SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE ' 
    + QUOTENAME(OBJECT_SCHEMA_NAME(f.parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(f.parent_object_id))
    + ' DROP CONSTRAINT ' + QUOTENAME(f.name) + ';'
FROM sys.foreign_keys AS f
INNER JOIN @tables_to_delete AS t
ON f.referenced_object_id = OBJECT_ID(t.t);

-- then the DROP TABLE commands:

SELECT @sql += CHAR(13) + CHAR(10) + N'DROP TABLE '
    + t + ';'
FROM @tables_to_delete;

PRINT @sql;
-- EXEC sp_executesql @sql;

Result (the constraint names will look different if you run this):

ALTER TABLE [dbo].[b] DROP CONSTRAINT [FK__b__a__2D27B809];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__a__30F848ED];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__b__300424B4];
ALTER TABLE [dbo].[d] DROP CONSTRAINT [FK__d__c__32E0915F];
DROP TABLE dbo.a;
DROP TABLE dbo.b;
DROP TABLE dbo.c;

When you're happy about the result, uncomment the EXEC line.

(Note, you won't be able to validate the script in its entirety when using PRINT if the script is very large. The script is truncated by Management Studio because it still has an archaic limit to how many characters it will show. The string won't be truncated like this when it gets passed to sp_executesql.)

PostgreSQL – Replacing Table Content and Disabling Foreign Keys

The deferred constraints solution (see an example in the question) actually works if I make the keys deferrable in normal way.

This didn't work because I did UPDATE pg_catalog.pg_constraint SET condeferrable = true; but forgot to set the same for pg_catalog.pg_trigger.

Best Answer

Related Solutions

Sql-server – View foreign key constraints so I can delete a table

PostgreSQL – Replacing Table Content and Disabling Foreign Keys

Related Question