假如我们数据库中有两张表Person和Book
Person表：
CREATE TABLE [dbo].[Person](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
[CreateTime] [datetime] NULL,
[UpdateTime] [datetime] NULL,
CONSTRAINT [PK_Person] 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 ALTER TABLE [dbo].[Person] ADD CONSTRAINT [DF_Person_CreateTime] DEFAULT (getdate()) FOR [CreateTime]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Person] ON [dbo].[Person]
(
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Book表：
CREATE TABLE [dbo].[Book](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BookCode] [nvarchar](50) NULL,
[BookName] [nvarchar](50) NULL,
[PersonCode] [nvarchar](50) NULL,
CONSTRAINT [PK_Book] 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 ALTER TABLE [dbo].[Book] WITH CHECK ADD CONSTRAINT [FK_Book_Person] FOREIGN KEY([PersonCode])
REFERENCES [dbo].[Person] ([Code])
ON UPDATE CASCADE
ON DELETE CASCADE
GO ALTER TABLE [dbo].[Book] CHECK CONSTRAINT [FK_Book_Person]
GO
可以看到Person表和Book表是一对多关系，一个Person可以有多个Book，所以Book表的PersonCode列是外键，指向Person表的Code列，并为强制约束，也就是说Book表的PersonCode列的值，只能是Person表的Code列值，否则SQL Server会报错：
现在我们执行下面语句给两张表插入数据，我们将插入Person表和Book表的两个Insert语句写在了个事务transaction中，按道理其中一个Insert执行失败，另一个就不会执行：
BEGIN TRAN INSERT INTO Person([Code],[Name])
VALUES('P003','Jack') INSERT INTO [dbo].[Book]([BookCode],[BookName],[PersonCode])
VALUES
('B001','B001','P003'),
('B002','B002','P003'),
('B003','B003','P003'),
('B004','B004','P003'),
('B005','B005','XXX')--由于Book表的[PersonCode]列值'XXX'在Person表的[Code]列中不存在，所以整个INSERT INTO [dbo].[Book]语句会报错不会执行 COMMIT
由于值"XXX"在Person表的[Name]列中不存在，所以INSERT INTO [dbo].[Book]语句报错没有执行，但是我们意外地发现INSERT INTO Person却随着事务Commit一起提交了。。。Person表的数据被成功插入了。。。
(1 行受影响)
消息 547，级别 16，状态 0，第 7 行
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Book_Person". The conflict occurred in database "TestDB", table "dbo.Person", column 'Code'.
The statement has been terminated.
查询Person表数据：
查询Book表数据：
这是因为INSERT INTO [dbo].[Book]语句虽然报错没执行，但是最下面的Commit语句却执行了，也就是说INSERT INTO [dbo].[Book]语句报错，并没有阻止后面Commit语句的执行，所以INSERT INTO Person语句随着事务被提交到数据库生效了。。。
现在我们更改上面的语句如下，将两个Insert语句都放到try catch中：
BEGIN TRAN
BEGIN TRY
INSERT INTO Person([Code],[Name])
VALUES('P003','Jack')
INSERT INTO [dbo].[Book]([BookCode],[BookName],[PersonCode])
VALUES
('B001','B001','P003'),
('B002','B002','P003'),
('B003','B003','P003'),
('B004','B004','P003'),
('B005','B005','XXX')--由于Book表的[PersonCode]列值'XXX'在Person表的[Code]列中不存在，所以整个INSERT INTO [dbo].[Book]语句会报错不会执行
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
运行后，我们再查询Person表的数据：
查询Book表数据：
我们可以看到这次就和我们的预期一致了，在INSERT INTO [dbo].[Book]语句报错后，后面的Commit语句没有执行，执行了catch中的Rollback，最后两张表的数据都没有插入数据库。
