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语句有何诡异之处?