CODEWITHSUNDEEP
sqlsql-serversql-server-2008
RAKESH HOLKAR
RAKESH HOLKAR

Reputation: 2197

How can apply truncate command on a table which is referenced in another table?

I have two table masterTbl and detailTbl which structure is given below...

--PRIMARY TABLE
CREATE TABLE masterTbl (
id INT IDENTITY(1,1) CONSTRAINT pk_id PRIMARY KEY, 
name VARCHAR(100) NOT NULL, 
description VARCHAR(max))

--FORIGN TABLE
CREATE TABLE detailTbl (
id INT IDENTITY(1,1) PRIMARY KEY,
m_id INT NOT NULL CONSTRAINT fk_mid FOREIGN KEY REFERENCES masterTbl(id) ON DELETE CASCADE,
details VARCHAR(MAX))

I want to perform

TRUNCATE TABLE masterTbl

but it is encountering error "Cannot truncate table 'testDB.dbo.masterTbl' because it is being referenced by a FOREIGN KEY constraint."

How can i use TRUNCATE command on masterTbl??

Upvotes: 0

Views: 4667

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

You have to remove the FK constraint fk_mid. But you won't be able to put it back if the FK Constraint is not respected, cause the CASCADE REMOVE won't happen (risk of orphans)

Or use DELETE instead of TRUNCATE. Which will preserve the DELETE CASCADE behaviour.

EDIT

DELETE FROM dbo.masterTbl;
DBCC CHECKIDENT ('dbo.masterTbl', RESEED, 0) -- will set IDENTITY count back to 0

Upvotes: 3

Related Questions