How to truncate multiple tables if the table has dependencies

Question

I want to truncate multiple tables if the table has dependencies. I tried couple of solutions from StackOverflow e.g. Solution 1 but seems not working.

The table structure is below:

  CREATE TABLE AEvent (
        eID int NOT NULL,   
        startTime datetime NOT NULL,
        endTime datetime NULL,  
        CONSTRAINT PK_A_EVENT PRIMARY KEY NONCLUSTERED (eID)
    );
        
    CREATE TABLE AEParam (
        eID int NOT NULL REFERENCES AEvent(eID),
        name nvarchar (446) NOT NULL,
        value nvarchar (2048) NULL,
        CONSTRAINT PK_A_E_PARAM PRIMARY KEY NONCLUSTERED (eID, name)
    );

I tried using below query to truncate but seems not working.

SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME in ('AEvent','AEParam')

The result of your query won't truncate anything, you then have to run it. And please post links to the solutions you found. — Dale K, Jul 01 '21 at 00:12
Hi @DaleK, could you please provide me script to truncate both tables. As I mentioned there is dependencies. — RjLearn, Jul 01 '21 at 00:14
Or just copy this post https://dba.stackexchange.com/questions/190073/truncate-tables-with-dependent-foreign-key-constraints — Dale K, Jul 01 '21 at 00:15
Here is the link @DaleK. https://stackoverflow.com/questions/15926826/truncate-multiple-tables-in-one-mysql-statement — RjLearn, Jul 01 '21 at 00:16
Hi @DaleK, Could you please provide me solution for it. I can see many answers but not example of doing it. — RjLearn, Jul 01 '21 at 00:33
The 2 links provide all the information you need, dynamic scripts to truncate, and dynamic scripts to un-link and re-link your foreign keys to allow the truncation. I don't have time to write and test such a complex script for you - and writing such scripts is not the intention of this site. — Dale K, Jul 01 '21 at 00:54

score 0 · Accepted Answer · answered Jul 07 '21 at 23:18

I found the answer for this. This is how I truncated my table.

Truncate table AEParam
ALTER TABLE [dbo].[AEParam] DROP CONSTRAINT [FK_AEvent _AEParam]
Truncate table AEvent
ALTER TABLE [dbo].[AEParam]  WITH NOCHECK ADD CONSTRAINT [FK_AEvent_AEParam] FOREIGN KEY([eID])
REFERENCES [dbo].[AEvent] ([eID])

How to truncate multiple tables if the table has dependencies

1 Answers1