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

idINTIDENTITY(0, 1)CONSTRAINT pk_addressPRIMARYKEY,

zipSMALLINTNOTNULLCONSTRAINT chk_zipCHECK(zip> 0),

streetNVARCHAR(50)NOTNULLCONSTRAINT chk_streetCHECK(LEN(street)> 0),

numberSMALLINTNOTNULLCONSTRAINT chk_numberCHECK(number> 0)

CONSTRAINT unq_addressUNIQUE(zip, street, number)

);

CREATETABLE [user]

emailVARCHAR(50)CONSTRAINT pk_userPRIMARYKEY,

addressINTNULLCONSTRAINT fk_user_addressFOREIGNKEYREFERENCES address(id)ONDELETESETNULL

);

-- Populating tables

INSERTINTO addressVALUES(2365,'street 1', 2);

INSERTINTO addressVALUES(23369,'street 2', 2);

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

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

-- Truncate query that fails

TRUNCATETABLE address;

-- Delete query that passes

--DELETE address;

[4655 byte] By [intenion] at [2007-12-24]