ECNU数据库作业——Lab5

Lab 5

EX1

To execute the following statements to create table t1 and three views v1, v2, and v3:

CREATE TABLE t1 (
	c INT
);

CREATE OR REPLACE VIEW v1
AS SELECT c FROM t1 WHERE c > 10;

CREATE OR REPLACE VIEW v2
AS SELECT c FROM v1
WITH CASCADED CHECK OPTION;

CREATE OR REPLACE VIEW v3
AS SELECT c FROM v2 WHERE c < 20;

Please indicate whether the following four statements can be executed successfully and provide an explanation for each.

INSERT INTO v1(c) VALUES (5);
INSERT INTO v2(c) VALUES (5);
INSERT INTO v3(c) VALUES (8);
INSERT INTO v3(c) VALUES (30);

在数据库中运行上述创建和插入代码，结果如下：

INSERT INTO v1(c) VALUES (5)
> Affected rows: 1
> 查询时间: 0.024s

INSERT INTO v2(c) VALUES (5)
> 1369 - CHECK OPTION failed 'lab5.v2'
> 查询时间: 0s

INSERT INTO v3(c) VALUES (8)
> 1369 - CHECK OPTION failed 'lab5.v3'
> 查询时间: 0.001s

INSERT INTO v3(c) VALUES (30)
> Affected rows: 1
> 查询时间: 0.024s

可以看到第1、4条插入语句运行成功，而第2、3条语句运行失败，

1. v1视图创建时没有with check option，因此不会拒绝不满足where子句的值插入，因此可以插入成功