提要:本教程将向您介绍如何使用WITH CHECK OPTION子句创建可更新视图,以确保通过视图对基表进行的更改满足视图定义条件。

WITH CHECK OPTION 简介

在之前的可更新视图部分,你学习到了如何创建一个可更新视图,它允许你通过视图来修改基表的数据。

让我们来看一下示例数据库里的 citycountry 表。

下面的语句创建了一个名为 usa_city 的可更新视图,它返回所有的美国城市。

CREATE VIEW usa_city AS SELECT
 city_id,
 city,
 country_id
FROM
 city
WHERE
 country_id = 103
ORDER BY
 city;

下面的语句,通过 usa_city 视图,将数据插入到 city 表中:

INSERT INTO usa_city (city, country_id)
VALUES
 ('Birmingham', 102);

问题是插入的新行在视图中不可见。这可能会造成安全问题,因为我们可能会授权用户更新美国(country_id = 103)的城市,而不是英国(country_id = 102)。

为防止用户插入或更新通过视图不可见的行,在创建视图时使用 WITH CHECK OPTION 子句。

下面修改 usa_city 视图,让它带上 WITH CHECK OPTION 子句:

CREATE
OR REPLACE VIEW usa_city AS SELECT
 city_id,
 city,
 country_id
FROM
 city
WHERE
 country_id = 103
ORDER BY
 city WITH CHECK OPTION;

现在,我们再尝试插入英国的城市,将会报错:

ERROR:  new row violates check option for view "usa_city"
DETAIL:  Failing row contains (604, Cambridge+, 102, 2016-07-02 08:41:01.828561).

下面的语句尝试将 city_id 为 135 的城市修改为英国:

UPDATE usa_city
SET country_id = 102
WHERE
 city_id = 135;

PostgreSQL 拒绝更新,并抛出错误:

ERROR:  new row violates check option for view "usa_city"
DETAIL:  Failing row contains (135, Dallas, 102, 2016-07-02 10:37:27.466176).

这是因为 UPDATE 语句导致正在更新的行,在 usa_city 视图中不可见。

用 LOCAL 和 CASCADED 检查范围

首先,创建一个视图,返回以字母A开头的所有城市:

CREATE VIEW city_a AS SELECT
 city_id,
 city,
 country_id
FROM
 city
WHERE
 city LIKE 'A%';

city_a 视图没有 WITH CHECK OPTION 子句。

然后,创建一个 city_a_usa 视图,返回以字母A开头的所有美国城市。这个视图基于 city_a 视图:

CREATE
OR REPLACE VIEW city_a_usa AS SELECT
 city_id,
 city,
 country_id
FROM
 city_a
WHERE
 country_id = 103 
WITH CASCADED CHECK OPTION;

city_a_usa 视图有一个 WITH CASCADED CHECK OPTION 子句。注意其中的 CASCADED 选项。

下面的语句通过 city_a_usa 视图往 city 表里插入数据:

INSERT INTO city_a_usa (city, country_id)
VALUES
 ('Houston', 103);

PostgreSQL 拒绝了插入,并抛出以下错误:

ERROR: new row violates check option for view "city_a"
SQL state: 44000
Detail: Failing row contains (605, Houston, 103, 2016-07-02 09:51:40.916855).

该错误消息指出,即使 city_a 视图没有 WITH CHECK OPTION 子句,也违反了 city_a 视图的定义条件。

这是因为,当我们给 city_a_usa 视图使用了 WITH CASCADED CHECK OPTION 后,PostgreSQL 会检查 city_a_usa 视图及其底层视图的定义条件,在这个例子中,就是 city_a 视图。

如果只对该视图进行检查,可以像下面的例子这样使用 WITH LOCAL CHECK OPTION

CREATE OR REPLACE VIEW city_a_usa AS SELECT
 city_id,
 city,
 country_id
FROM
 city_a
WHERE
 country_id = 103 
WITH LOCAL CHECK OPTION;

现在,再尝试插入:

INSERT INTO city_a_usa (city, country_id)
VALUES
 ('Houston', 103);

这次成功了,因为视图定义的条件只作用于当前视图,PostgreSQL 不会使用该条件检查基视图。

上一篇:物化视图
下一篇:递归视图