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

IF OBJECT_ID('user') IS NOT NULL

DROP TABLE [user];

IF OBJECT_ID('address') IS NOT NULL

DROP TABLE address;

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

CREATE TABLE address

(

id INT IDENTITY (0, 1) CONSTRAINT pk_address PRIMARY KEY,

zip SMALLINT NOT NULL CONSTRAINT chk_zip CHECK (zip > 0),

street NVARCHAR(50) NOT NULL CONSTRAINT chk_street CHECK (LEN(street) > 0),

number SMALLINT NOT NULL CONSTRAINT chk_number CHECK (number > 0)

CONSTRAINT unq_address UNIQUE (zip, street, number)

);

Go

CREATE TABLE [user]

(

email VARCHAR(50) CONSTRAINT pk_user PRIMARY KEY,

address INT NULL CONSTRAINT fk_user_address FOREIGN KEY REFERENCES address (id) ON DELETE SET NULL

);

Go

-- Populating tables

INSERT INTO address VALUES (2365, 'street 1', 2);

INSERT INTO address VALUES (23369, 'street 2', 2);

Go

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

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

Go

-- Truncate query that fails

TRUNCATE TABLE 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