Msg 4712: cannot truncate table

Hello,

I am running SQL Server 2005 SP1. Whenever I issue a 'truncate' query to the DB engine it fails if the table to be modified is currently referenced by a foreign key constraint.

A code snippet (T-SQL) that demonstrates the problem is beneath.

Does anybody know a fix-it?

Thanks in advance,
Intenion

---

USE tempdb

Go

-- Clearing the 'tmpdb' database

IFOBJECT_ID('user')ISNOTNULL

DROPTABLE [user];

IFOBJECT_ID('address')ISNOTNULL

DROPTABLE address;

-- Creating tables: 'address' and 'user'

CREATETABLE address

(

id INTIDENTITY(0, 1)CONSTRAINT pk_address PRIMARYKEY,

zip SMALLINTNOTNULLCONSTRAINT chk_zip CHECK(zip > 0),

street NVARCHAR(50)NOTNULLCONSTRAINT chk_street CHECK(LEN(street)> 0),

number SMALLINTNOTNULLCONSTRAINT chk_number CHECK(number > 0)

CONSTRAINT unq_address UNIQUE(zip, street, number)

);

Go

CREATETABLE [user]

(

email VARCHAR(50)CONSTRAINT pk_user PRIMARYKEY,

address INTNULLCONSTRAINT fk_user_address FOREIGNKEYREFERENCES address (id)ONDELETESETNULL

);

Go

-- Populating tables

INSERTINTO address VALUES(2365,'street 1', 2);

INSERTINTO address VALUES(23369,'street 2', 2);

Go

INSERTINTO [user] VALUES('someone@.isp.net', 0);

INSERTINTO [user] VALUES('somebody@.isp-r-us.com', 1);

Go

-- Truncate query that fails

TRUNCATETABLE address;

-- Delete query that passes

--DELETE address;

Hi,

this is by design. YOu either have to drop the FK first or use the DELETE statement.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Maybe a documentation update would be welcome for the 'truncate table' topic in Books Online.

Thank you for your answer,
Intenion.

|||

Jens K. Suessmeyer wrote:

Hi,

this is by design. YOu either have to drop the FK first or use the DELETE statement.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

I think I am running into the same situation. Would you mind showing how (using SMO) that I would drop the FK (then readd it so that the table is not changed)? It seems from the documentation that DELETE also tries to enforce the FK constraint so the error would be reasserted if I used DELETE.

Kevin