使用 WITH CHECK OPTION 子句创建可更新视图

总结：在本教程中，我们将向您展示如何使用 WITH CHECK OPTION 子句创建可更新视图，以确保通过视图对基表的更改满足视图定义条件。

WITH CHECK OPTION 子句简介

在创建可更新视图教程中，您学习了如何创建可更新视图，该视图允许您通过视图更改基表的数据。

我们来看看 PostgreSQL Sakila 示例数据库中的 city 和 country 表。

以下语句创建一个名为 usa_city 的可更新视图，该视图返回美国的所有城市。

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

以下语句 city 通过 usa_city向表中插入新行。

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

INSERT INTO usa_city (city, country_id)
VALUES ('Cambridge', 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;

现在，运行以下语句为该 United Kingdom 国家/地区插入另一个城市。

PostgreSQL 拒绝插入并发出错误。

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

它按预期工作。

以下语句将城市 ID 135 的国家/地区更新为 United Kingdom .

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 的条款。

其次，创建另一个视图，返回名称以字母 A 开头并位于 United States . 此 city_a_usa 视图基于 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 city_a_usa

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 即使 city_a 视图没有该 WITH CHECK OPTION 子句，也违反了视图的视图定义条件。

这是因为当我们使用 WITH CASCADED CHECK OPTION for city_a_usa 视图时，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;

让我们再次通过 city_a_usa 视图在 city 表中插入一个新行。

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

这次成功是因为新行满足视图的视图定义条件 city_a_usa 。PostgreSQL 没有检查基本视图的视图定义条件。

在本教程中，您学习了如何在 WITH CHECK OPTION 通过视图对基础表进行更改时使用检查视图定义条件的子句创建可更新视图。

WITH CHECK OPTION 子句简介

LOCAL 和 CASCADED 的检查范围

本页目录