简单易懂解决SQL Server使用视图插入数据失败问题--WITH CHECK OPTION

前言

学习视图插入数据遇到问题，查阅博文，刚开始都没看懂，耗费了大量时间，有感于其他博友对这个问题的解释不太友好，只有懂了的才能看懂，不懂想看懂很困难，本着加深记忆的思想，开始撰写这篇博文。

---

准备

先提供表和插入的数据

--3.5 建立一个“学生”表Student
CREATE TABLE Student
(
	Sno CHAR(9) PRIMARY KEY,	--列级完整性约束条件，Sno是主码
	Sname CHAR(20) UNIQUE,		--Sname取唯一值
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20),
);

INSERT INTO Student VALUES('201215121','李勇','男',20,'CS')
INSERT INTO Student VALUES('201215122','刘晨','女',19,'CS')
INSERT INTO Student VALUES('201215123','王敏','女',18,'MA')
INSERT INTO Student VALUES('201215124','欧阳阳','女',20,'WX')
INSERT INTO Student VALUES('201215125','张立','男',19,'IS')
INSERT INTO Student VALUES('201215012','赵阳','男','20','IS')

SELECT * FROM Student

插入后的结果集：

---

创建视图

创建视图的代码：

CREATE VIEW IS_Student 
  AS SELECT Sno,Sname,Sage FROM Student 
  WHERE Sdept='IS' WITH CHECK OPTION -- 使用了WITH CHECK OPTION
GO
SELECT * FROM IS_Student
GO

视图的结果集：

---

插入数据失败

使用视图插入数据：

INSERT INTO IS_Student VALUES('201215129','赵新',20)

插入失败，失败结果是：

消息 550，级别 16，状态 1，第 1 行
试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。

---

失败原因分析

视图中加入了 WITH CHECK OPTION 后，以后对视图进行插入、修改和删除操作时，都会对语句的Sdept字段进行判断，如果Sdept=‘IS’，即满足条件，那么才可以进行该语句的操作。否则，即不满足Sdept=‘IS’，也就是说语句中指定的元组的Sdept的值不等于IS，那么就不能进行该操作。

比如该语句的插入，根本没有指定Sdept的值，只插入了数据(Sno,Sname,Sage)，该元组的Sdept值是null，显然是不等于’IS’的，所以就会插入失败，就会显示上面的情况。

---

解决

那么怎么才能实现插入该元组呢？

('201215129','赵新',20)

将要用到的查询语句：

SELECT * FROM IS_Student -- 查询视图
SELECT * FROM Student    -- 查询表

---

多种解决方法

---

方法一：修改视图，去掉WITH CHECK OPTION

ALTER VIEW IS_Student 
AS 
SELECT Sno,Sname,Sage FROM Student WHERE Sdept='IS' --WITH CHECK OPTION
GO
INSERT INTO IS_Student VALUES('201215129','赵新',20)

下面的结果集中，第一张图片是视图的查询结果，第二张是表的查询结果。
结果集：

---

方法二：修改视图，增添Sdept属性列

ALTER VIEW IS_Student 
AS 
SELECT Sno,Sname,Sage,Sdept FROM Student WHERE Sdept='IS' WITH CHECK OPTION
GO
INSERT INTO INSERT INTO IS_Student VALUES('201215129','赵新',20,'IS')

这时元组中的属性Sdept=‘IS’，满足条件，可以插入成功。
结果集：

---

方法三：使用基本表插入

--INSERT INTO Student(Sno,Sname,Sage) VALUES ('201215129','赵新',20)
--或
INSERT INTO Student(Sno,Sname,Sage,Sdept) VALUES ('201215129','赵新',20,'IS')

---

后记

注1：该问题起始于《数据库系统概论》中的例3.85、例3.96。
注2：新人一枚，个人认为讲的还是比较透彻的，希望大家可以支持一下 ，点赞收藏啦！