Truncate Table和Delete的简单比较 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语句有何诡异之处?

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值