PostgreSQL 教程: 使用 WITH CHECK OPTION 子句创建可更新视图

九月 9, 2023

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

WITH CHECK OPTION 子句简介

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

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

以下语句创建一个名为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);

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视图中不可见。

首先，创建一个视图，返回名称以字母 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_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;

让我们再次通过city_a_usa视图将新行插入到city表中。

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

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

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