一个关于truncate的问题

[武哥博文]

use tempdb;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
 [ID] [uniqueidentifier] NOT NULL,
 [Name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
 [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Table1Ref](
 [ID] [uniqueidentifier] NOT NULL,
 [RefID] [uniqueidentifier] NOT NULL,
 [DateAssigned] [smalldatetime] NOT NULL,
 CONSTRAINT [UserRoleID] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
GO
ALTER TABLE [dbo].[Table1Ref]  WITH CHECK ADD FOREIGN KEY([RefID])
REFERENCES [dbo].[Table1] ([ID])

insert into Table1(ID, Name)
select newId(), 'A'
union
select newId(), 'B'
union
select newId(), 'C'

insert into Table1Ref(ID, RefID,DateAssigned)
select newid(),ID, getdate() from Table1

-----------clear data begin----
truncate table Table1Ref
truncate table Table1
-----------clear data end----

此时SQL Server会报出如下错误：
Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'Table1' because it is being referenced by a FOREIGN KEY constraint.

如果将clear data代码改成使用delete则执行成功
-----------clear data begin----
delete Table1Ref
delete Table1
-----------clear data end----
不知道truncate语句有何诡异之处?

[--小F--]

有主外键约束引用的表不能使用TRUNCATE

[武哥博文]

提示错误是很清楚，那么delete为什么就没有这样的错误。truncate同样是清空所有的数据，而且理论上比delete清的更彻底！

[jack_ailly]

限制（联机丛书）
不能对以下表使用 TRUNCATE TABLE：
1.由 FOREIGN KEY 约束引用的表。（您可以截断具有引用自身的外键的表。）
2.参与索引视图的表。
3.通过使用事务复制或合并复制发布的表。
  
注：对于具有以上一个或多个特征的表，请使用 DELETE 语句。

[pt1314917]

truncate的限制
不能对以下表使用 TRUNCATE TABLE：

由 FOREIGN KEY 约束引用的表。


参与索引视图的表。


通过使用事务复制或合并复制发布的表。

[武哥博文]

找到了
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

http://msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx

[csdyyr]

学习。

[jack_ailly]

[Quote=引用 12 楼 grandtree 的回复:]
找到了
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

http://msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx
[/Quote]TRUNCATE TABLE 不能激活触发器，因为该操作不记录各个行删除。(联机丛书..)

[ziqing_1_2_3]

学习了

[aimee_99]

不能对以下表使用 TRUNCATE TABLE：

由 FOREIGN KEY 约束引用的表。

[子陌红尘]

try:

truncate table Table1Ref
go
truncate table Table1
go

[nalnait]

有引用的表不能 TRUNCATE

[nalnait]

[Quote=引用 2 楼 libin_ftsafe 的回复:]
try:

SQL codetruncatetable Table1Refgotruncatetable Table1go
[/Quote]
只要存在主外键关系就不行

[jack_ailly]

有外键约束。

[武哥博文]

楼上的试过吗，加go依然不管用的，我试过，使用两个连接执行truncate table Table1Ref
和truncate table Table1都不管用

[jack_ailly]

Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'Table1' because it is being referenced by a FOREIGN KEY constraint.
错误提示很清楚啊。