0Day Forums
Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - Printable Version

+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? (/Thread-Cannot-truncate-table-because-it-is-being-referenced-by-a-FOREIGN-KEY-constraint)

Pages: 1 2 3

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - seyfollahhfig - 07-31-2023

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?

I know that I can either

* Use a `DELETE` without a where clause and then `RESEED` the identity (or)
* Remove the FK, truncate the table, and recreate the FK.

I thought that as long as I truncated the child table before the parent, I'd be okay without doing either of the options above, but I'm getting this error:

> Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.

RE: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - dinhxwmdoz - 07-31-2023

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][1] for more details.


[1]:

[To see links please register here]



RE: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - linneazwob - 07-31-2023

Here is a script I wrote in order to automate the process. I hope it helps.

SET NOCOUNT ON

-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

-- 1 = Will not execute statements
SET @Debug = 0
-- 0 = Will not create or truncate storage table
-- 1 = Will create or truncate storage table
SET @Recycle = 0
-- 1 = Will print a message on every step
set @Verbose = 1

SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'

-- Drop Temporary tables
DROP TABLE #FKs

-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
OBJECT_NAME(constraint_object_id) as ConstraintName,
OBJECT_NAME(parent_object_id) as TableName,
clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
clm2.name as ReferencedColumnName
INTO #FKs
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1
ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_id
JOIN sys.columns clm2
ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_id
WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
ORDER BY OBJECT_NAME(parent_object_id)


-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
IF @Verbose = 1
PRINT '1. Creating Process Specific Tables...'

-- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
CREATE TABLE [Internal_FK_Definition_Storage]
(
ID int not null identity(1,1) primary key,
FK_Name varchar(250) not null,
FK_CreationStatement varchar(max) not null,
FK_DestructionStatement varchar(max) not null,
Table_TruncationStatement varchar(max) not null
)
END
ELSE
BEGIN
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '1. Truncating Process Specific Tables...'

-- TRUNCATE TABLE IF IT ALREADY EXISTS
TRUNCATE TABLE [Internal_FK_Definition_Storage]
END
ELSE
PRINT '1. Process specific table will be recycled from previous execution...'
END

IF @Recycle = 0
BEGIN

IF @Verbose = 1
PRINT '2. Backing up Foreign Key Definitions...'

-- Fetch and persist FKs
WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
BEGIN
SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)

INSERT INTO [Internal_FK_Definition_Storage]
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp

SET @i = @i + 1

IF @Verbose = 1
PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'

END
END
ELSE
PRINT '2. Backup up was recycled from previous execution...'

IF @Verbose = 1
PRINT '3. Dropping Foreign Keys...'

-- DROP FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)

IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)

SET @i = @i + 1

IF @Verbose = 1
PRINT ' > Dropping [' + @ConstraintName + ']'
END

IF @Verbose = 1
PRINT '4. Truncating Tables...'

-- TRUNCATE TABLES
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)

SET @i = @i + 1

IF @Verbose = 1
PRINT ' > ' + @Statement
END

IF @Verbose = 1
PRINT '5. Re-creating Foreign Keys...'

-- CREATE FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)

SET @i = @i + 1

IF @Verbose = 1
PRINT ' > Re-creating [' + @ConstraintName + ']'
END

IF @Verbose = 1
PRINT '6. Process Completed'




RE: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - rosabelvxdgwt - 07-31-2023

It's my solution of this issue. I used it for altering PK, but idea the same. Hope this will be useful)

PRINT 'Script starts'

DECLARE @foreign_key_name varchar(255)
DECLARE @keycnt int
DECLARE @foreign_table varchar(255)
DECLARE @foreign_column_1 varchar(255)
DECLARE @foreign_column_2 varchar(255)
DECLARE @primary_table varchar(255)
DECLARE @primary_column_1 varchar(255)
DECLARE @primary_column_2 varchar(255)
DECLARE @TablN varchar(255)

-->> Type the primary table name
SET @TablN = ''
--------------------------------------------------------------------------------------- ------------------------------
--Here will be created the temporary table with all reference FKs
---------------------------------------------------------------------------------------------------------------------
PRINT 'Creating the temporary table'
select cast(f.name as varchar(255)) as foreign_key_name
, r.keycnt
, cast(c.name as varchar(255)) as foreign_table
, cast(fc.name as varchar(255)) as foreign_column_1
, cast(fc2.name as varchar(255)) as foreign_column_2
, cast(p.name as varchar(255)) as primary_table
, cast(rc.name as varchar(255)) as primary_column_1
, cast(rc2.name as varchar(255)) as primary_column_2
into #ConTab
from sysobjects f
inner join sysobjects c on f.parent_obj = c.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects p on r.rkeyid = p.id
inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
where f.type = 'F' and p.name = @TablN
ORDER BY cast(p.name as varchar(255))
---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will drop all reference FKs
---------------------------------------------------------------------------------------------------------------------
DECLARE @CURSOR CURSOR
/*Fill in cursor*/

PRINT 'Cursor 1 starting. All refernce FK will be droped'

SET @CURSOR = CURSOR SCROLL
FOR
select foreign_key_name
, keycnt
, foreign_table
, foreign_column_1
, foreign_column_2
, primary_table
, primary_column_1
, primary_column_2
from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC ('ALTER TABLE ['+@foreign_table+'] DROP CONSTRAINT ['+@foreign_key_name+']')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 1 finished work'
---------------------------------------------------------------------------------------------------------------------
--Here you should provide the chainging script for the primary table
---------------------------------------------------------------------------------------------------------------------

PRINT 'Altering primary table begin'

TRUNCATE TABLE table_name

PRINT 'Altering finished'

---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will add again all reference FKs
--------------------------------------------------------------------------------------------------------------------

PRINT 'Cursor 2 starting. All refernce FK will added'
SET @CURSOR = CURSOR SCROLL
FOR
select foreign_key_name
, keycnt
, foreign_table
, foreign_column_1
, foreign_column_2
, primary_table
, primary_column_1
, primary_column_2
from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC ('ALTER TABLE [' +@foreign_table+ '] WITH NOCHECK ADD CONSTRAINT [' +@foreign_key_name+ '] FOREIGN KEY(['+@foreign_column_1+'])
REFERENCES [' +@primary_table+'] (['+@primary_column_1+'])')

EXEC ('ALTER TABLE [' +@foreign_table+ '] CHECK CONSTRAINT [' +@foreign_key_name+']')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 2 finished work'
---------------------------------------------------------------------------------------------------------------------
PRINT 'Temporary table droping'
drop table #ConTab
PRINT 'Finish'

RE: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - amalitarml - 07-31-2023

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE table1;
TRUNCATE table2;
SET FOREIGN_KEY_CHECKS=1;


reference -

[To see links please register here]


Working for me in MYSQL

RE: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - Franklynqwuaqqjfk - 07-31-2023

You could try `DELETE FROM <your table >;`.

The server will show you the name of the restriction and the table, and deleting that table you can delete what you need.

RE: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - groundberry991998 - 07-31-2023

Found elsewhere on the web

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
-- EXEC sp_MSForEachTable 'DELETE FROM ?' -- Uncomment to execute
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

RE: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - gastaldite443203 - 07-31-2023

You cannot truncate a table if you don't drop the constraints. A disable also doesn't work. you need to Drop everything. i've made a script that drop all constrainsts and then recreate then.

Be sure to wrap it in a transaction ;)

SET NOCOUNT ON
GO

DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--DROP CONSTRAINT:

DECLARE @dynSQL varchar(MAX);

DECLARE cur CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
'
FROM
@table

OPEN cur

FETCH cur into @dynSQL
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@dynSQL)
print @dynSQL

FETCH cur into @dynSQL
END
CLOSE cur
DEALLOCATE cur
---------------------



--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!

truncate table your_table

--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!

---------------------
--ADD CONSTRAINT:

DECLARE cur2 CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
'
FROM
@table

OPEN cur2

FETCH cur2 into @dynSQL
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@dynSQL)

print @dynSQL

FETCH cur2 into @dynSQL
END
CLOSE cur2
DEALLOCATE cur2


RE: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - turment593579 - 07-31-2023

For `MS SQL`, at least the newer versions, you can just disable the constrains with code like this:

ALTER TABLE Orders
NOCHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO

TRUNCATE TABLE Customers
GO

ALTER TABLE Orders
WITH CHECK CHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO

RE: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? - knowledgeability143548 - 07-31-2023

The following works for me even with FK constraints, and combines the following answers to **only drop the specified tables**:

* [Transaction Automatic Rollback](

[To see links please register here]

)
* [Looping through comma-separated list](

[To see links please register here]

)
* [Executing Dynamic SQL (with table names from variable)](

[To see links please register here]

)
* [DELETE and RESEED table](

[To see links please register here]

) _(in this thread)_

----

USE [YourDB];

DECLARE @TransactionName varchar(20) = 'stopdropandroll';

BEGIN TRAN @TransactionName;
set xact_abort on; /* automatic rollback

[To see links please register here]

*/
-- ===== DO WORK // =====

-- dynamic sql placeholder
DECLARE @SQL varchar(300);

-- LOOP:

[To see links please register here]

-- list of things to loop
DECLARE @delim char = ';';
DECLARE @foreach varchar(MAX) = 'Table;Names;Separated;By;Delimiter' + @delim + 'AnotherName' + @delim + 'Still Another';
DECLARE @token varchar(MAX);
WHILE len(@foreach) > 0
BEGIN
-- set current loop token
SET @token = left(@foreach, charindex(@delim, @foreach+@delim)-1)
-- ======= DO WORK // ===========

-- dynamic sql (parentheses are required):

[To see links please register here]

SET @SQL = 'DELETE FROM [' + @token + ']; DBCC CHECKIDENT (''' + @token + ''',RESEED, 0);'; --

[To see links please register here]

PRINT @SQL;
EXEC (@SQL);

-- ======= // END WORK ===========
-- continue loop, chopping off token
SET @foreach = stuff(@foreach, 1, charindex(@delim, @foreach+@delim), '')
END

-- ===== // END WORK =====
-- review and commit
SELECT @@TRANCOUNT as TransactionsPerformed, @@ROWCOUNT as LastRowsChanged;
COMMIT TRAN @TransactionName;

----
**Note:**

I think it still helps to declare the tables in the order you want them deleted (i.e. kill dependencies first). As seen in [this answer](

[To see links please register here]

), rather than loop specific names you could substitute all tables with

EXEC sp_MSForEachTable 'DELETE FROM ?; DBCC CHECKIDENT (''?'',RESEED, 0);';