【转】关于创建视图的check option三个选项的作用

http://www.db2china.net/home/space.php?uid=117507&do=blog&id=31561

1、创建视图时有三个check option选项

with local check option

with cascaded check option

with check option

2、创建视图时各选项的最终效果

2.1 准备基础数据

create table t1  (id int);

insert into t1 values(1),(2),(3),(4),(5);

 

2.2 with local check option选项

create view v1 as select * from t1 where id<4;

create view v2 as select * from v1 with local option;

insert into v2 values(6);

注：插入成功

create view v3 as select * from v1 where id<4 with local option;

insert into v3 values(7);

注：插入失败

 

结论：with local option选项只对当前有约束条件的生效

 

2.3 with cascaded check option选项

create view v4 as select * from v1 with cascaded check option;

insert into v4 values(8);

注：插入失败

 

 

结论：with cascaded check option选项继承上一视图的查询条件，如v4视图。

 

2.4 with check option选项

create view v5 as select * from v1 with check option;

insert into v5 values(9);

注：插入失败

 

create view v6 as select * from t1 where id<4 with check option;

insert into v6 values(10);

注：插入失败

 

结论：with check option选项在任何时刻，只要有查询约束条件都要进行匹配。