引用完整性教程 & 引用完整性表的破解

来自 PostgreSQL wiki
跳转到导航跳转到搜索

作者：Joel Burton
最后更新时间：2001 年 9 月 3 日

介绍

引用完整性是数据库的一个功能，它确保数据库中隐含的关系得到强制执行。它是大多数数据库系统的功能，可以保护用户免受意外（或有意！）创建数据库中不一致的风险。

在本章中，我们将使用一组示例数据，其中包含人员、他们负责的任务以及与他们安排的约会。当您解雇员工时，您会将他们从您的人员表中删除，但希望确保您无法留下没有人负责的任务，或留下找不到人员姓名的时间安排。

如果您不熟悉主键和外键的概念，建议您复习一下基本的数据库入门知识，因为本教程假设您已经了解这些概念。

让我们开始吧！

首先，因为我们将使用系统表，最好在一个练习数据库中进行 

# CREATE DATABASE RI_test;
# \c ri_test

现在，让我们创建练习表。我们将有一个父表“pers”和两个子表“tasks”和“appts”。

父表很简单 

# CREATE TABLE pers (pid INT NOT NULL PRIMARY KEY,
    pname TEXT NOT NULL);

第一个子表将保存人员负责的任务 

# CREATE TABLE tasks (taskid SERIAL NOT NULL PRIMARY KEY,
    pid INT NOT NULL
      CONSTRAINT tasks__ref_p
        REFERENCES pers,
    task TEXT NOT NULL);

几点说明 

  • 外键引用由 PostgreSQL 作为一种约束处理。因此，我们可以用 CONSTRAINT <约束名称> 开头引用声明。如果这样做，PostgreSQL 生成的错误消息将返回此错误名称，用户可以方便地看到它，或者我们可以在前端系统中解析和处理它。通过将其包装在系统标识符（"）中，我们甚至可以使其看起来像一条错误消息（即 CONSTRAINT "Table tasks refers to table pers"），但我们对此名称/消息的长度有限，因此它不那么有用。约束名称不是必需的，我们可以省略它（即 ...pid INT NOT NULL REFERENCES pers...）。一切仍然相同，除了我们的约束没有名称，因此错误消息更加通用。
  • 在 REFERENCES 后面是我们要引用的表的名称，以及可选的括号中的字段名称。如果该字段是主键，则字段名称是可选的。如果我们没有将 'pid' 字段设置为 'pers' 的主键，我们将必须使用 'REFERENCES pers(pid)'。
  • 我们可以选择通过添加 ON DELETE 和 ON UPDATE 声明来通知 PostgreSQL 如何处理表 'pers' 上的删除和更新。如果这些没有被具体指定，那么 PostgreSQL 默认使用 “no action”，这（在撰写本文时，2000 年 12 月）与 “restrict” 相同。这意味着如果存在依赖于父项的子项，我们既不能删除父项，也不能更新父项的 'pid' 字段。在我们的另一个子表中，我们将看到另一种处理方法。
  • 我们可以选择告诉 PostgreSQL 这是否是 “可延迟的”，也就是说，如果我们允许在事务内部违反引用完整性，只要我们在事务结束时修复了我们的完整性问题。我们将在下面的 DEFERRING 中看到此示例。

因此，让我们添加一些示例数据 

INSERT INTO pers VALUES (1, 'Jeff Brown');
INSERT INTO pers VALUES (2, 'Maria Lane');

INSERT INTO tasks (pid, task) VALUES (1, 'Write contract');
INSERT INTO tasks (pid, task) VALUES (1, 'Upgrade database');

到目前为止一切顺利。

如果我们尝试插入一个没有父项的子项，即 

INSERT INTO tasks (pid, task) VALUES (3, 'Install Linux');

我们将收到一个引用完整性错误。此错误将引用我们的约束名称（如果有），并将阻止将此数据输入 'tasks'。此外，如果这是事务的一部分，它将回滚整个事务，如 PostgreSQL 中始终发生的那样。

此外，如果我们尝试更改一个有子项的父项 

DELETE FROM pers WHERE pname = 'Jeff Brown';

我们将遇到同样的问题，因为我们无法在 Jeff 被分配了任务的情况下删除他。

这将起作用，因为目前 Maria 没有子任务 

DELETE FROM pers WHERE pname = 'Maria Lane';

如果您执行了上述步骤，您需要添加 Maria 回来以供后面的示例使用 

INSERT INTO pers VALUES (2, 'Maria Lane');

默认（NO ACTION）或 RESTRICT - 对 PostgreSQL 来说意味着相同的事 - ON UPDATE 和 ON DELETE 规则在许多情况下是有意义的。您不希望能够从您的数据库中删除一名员工，即使他们拥有某些责任，否则您将永远不知道谁负责不同的任务。

一个更宽容的子表：约会

让我们创建一个第二个子表 'appts'（Appointment 的缩写） 

# CREATE TABLE appts (apptid SERIAL NOT NULL PRIMARY KEY,
    pid INT NOT NULL
      CONSTRAINT appt__ref_pers
        REFERENCES pers
          ON UPDATE CASCADE
          ON DELETE CASCADE
          DEFERRABLE,
    apptsubj TEXT NOT NULL,
    apptdate DATE NOT NULL);

并将一些数据插入其中 

INSERT INTO appts (pid, apptsubj, apptdate)
  VALUES (1, 'Discuss raise', '2001-01-15');

INSERT INTO appts (pid, apptsubj, apptdate)
  VALUES (2, 'Plan project', '2001-01-20');

这完全可以正常工作。同样，和之前一样 

INSERT INTO appts (pid, apptsubj, apptdate)
  VALUES (3, 'Termination', '2001-01-20');

无法工作，因为 'pers' 表中不存在 pid = 3。

然而，与 'tasks' 表不同，'appts' 被设置为级联删除和更新。这意味着如果我们更新了人员的 pid，或完全删除了人员，PostgreSQL 将允许并协助此操作，方法是首先删除或更新该人员存在的任何任务。

因此 

UPDATE pers SET pid=14 WHERE pid=2;

这将把 Maria 的 pid 更改为 14，首先更改 'appts' 表中连接的 pid。

DELETE FROM pers WHERE pid=14;

这将删除 Maria 的约会，然后删除 Maria。

其他关系

请注意，此类中的级联仅指 'pers' 和 'appt' 表之间的级联。尝试删除 Jeff 仍然会失败，因为即使 pers-appt 关系会级联，pers-tasks 关系也会失败，并且 PostgreSQL 会将删除尝试报告为违反此引用完整性。可能的动作

  • NO ACTION（默认值）
    如果更新或删除会失败引用完整性检查，则停止该操作。
  • RESTRICT
    目前与 NO ACTION 含义相同。
  • CASCADE
    删除子数据，然后删除父数据，这取决于其他关系，如上所述。
  • SET NULL
    将外键字段设置为 NULL，然后更新或删除父项。
  • SET DEFAULT
    将外键字段设置为其默认值，然后更新或删除父项。

SET NULL 和 SET DEFAULT 是有用的选项，特别是对于 ON DELETE。例如，我们可能有一个表 'offices'，用于跟踪员工使用哪个办公室。它可能包含 officeid、officelocation 和 persid 字段。如果我们想删除一个人，我们不应该仅仅因为这个人有一个相关的办公室而被阻止，但同样，我们也不希望仅仅因为这个人被删除而删除一个办公室。在某些情况下，最好的选择可能是将 'offices' 的 persid 字段设置为 NULL 或 DEFAULT，使办公室保持原样，但明确表明该办公室现在没有被使用。

请注意，ON UPDATE 和 ON DELETE 可以有不同的规则。例如，ON UPDATE CASCADE 但 ON DELETE RESTRICT 非常常见 - 允许人们更改他们的 ID，但不允许在存在相关人员的情况下删除任务。

还可以更改关系的动作，这需要在系统目录表中进行一些破解。请参阅下面的破解引用完整性。

延迟事务

默认情况下，会对每一种关系的每一次插入、删除或更新进行引用完整性检查，这些操作可能会影响这种关系。

这意味着 

INSERT INTO tasks (pid, task) VALUES (5, 'Open sales office');
INSERT INTO pers (pid, name) VALUES (5, 'Helen Kim');

会失败，因为在尝试第一次插入时，不存在 pid=5 的人员。在大多数情况下，这是最直观的设置。

但是，有时您可能无法预测数据到达的确切顺序。也许您正在接收从文本文件或网络加载的数据。数据可能不会按照上面的顺序到达（任务，然后是关联的人员）。在这种情况下，您可以选择延迟事务检查。

为了延迟事务，必须发生三件事 

  1. 引用完整性关系必须被定义为 DEFERRABLE。这不是默认值（NOT DEFERRABLE 是），因此必须显式声明，如上文针对 'appts' 所做的那样。
  2. 您必须在显式事务中。
  3. 您必须让此关系的初始默认值为 DEFERRED，或者为当前事务将关系约束设置为 DEFERRED。

对于表 'appts' 与 'pers' 的关系，我们将其声明为 DEFERRED。因此，如果我们这样做 

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO appts (pid, apptsubj, apptdate)
  VALUES (5, 'Negotiation', '2001-01-20');
INSERT INTO pers VALUES (5, 'Helen Kim');
COMMIT;

这将正常工作。

请注意，将关系声明为 DEFERRABLE 还不够 - 我们还必须使用 SET 将 CONSTRAINTS 显式设置为延迟。在此示例中，我们将所有关系都设置为 DEFERRED；相反，我们可以仅将单个约束设置为延迟，如下所示 

SET CONSTRAINTS appts__ref_pers DEFERRABLE;

预先设置关系可能很方便，而不是每次事务都必须设置它。为此，在 CONSTRAINT ... REFERENCES 声明中为表添加 "INITIALLY DEFERRED"。

例如 

# CREATE TABLE appts (apptid SERIAL NOT NULL PRIMARY KEY,
    pid INT NOT NULL
      CONSTRAINT appt__ref_pers
        REFERENCES pers
          ON UPDATE CASCADE
          ON DELETE CASCADE
          DEFERRABLE
          INITIALLY DEFERRED,
    apptsubj TEXT NOT NULL,
    apptdate DATE NOT NULL);

现在，我们可以简单地 

BEGIN;
INSERT INTO appts (pid, apptsubj, apptdate)
  VALUES (5, 'Negotiation', '2001-01-20');
INSERT INTO pers VALUES (5, 'Helen Kim');
COMMIT;

无需显式 SET 命令。如果在抽象 SQL 命令的编程环境中工作，并且很难执行任意、非标准的 SQL 命令（例如 “SET...”），则这尤其方便。

破解引用完整性

引用完整性在 PostgreSQL 中非常有效。但是，PostgreSQL 还没有 SQL 语法命令来更改操作、打开/关闭引用完整性等。

可以通过直接编辑存储在系统目录表中的信息来执行这些操作。

警告

要编辑系统目录表，您必须是 PostgreSQL 中的超级用户。此外，编辑这些表时要非常小心，确保先进行备份（通过 pg_dump）。意外的表级 UPDATE 或 DELETE 可能会删除所有表、破坏索引、损坏数据库等。通过在测试数据库中进行操作来练习，最好在没有其他重要数据库的机器上进行。

我们感兴趣的几个系统目录表 

pg_class
所有 “类”，包括表、视图、序列等。

pg_trigger
所有触发器。PostgreSQL 使用幕后触发器处理引用完整性，因此这里存储了所有引用完整性控制。

pg_proc
所有 PostgreSQL 过程。我们不需要对此进行任何更改，但我们将使用它来显示使用的引用完整性过程实际上是什么。

要查看（大多数）系统表，可以使用 psql 中的 \dS 命令。

例如，让我们从 pg_class 中收集有关测试表的信息 

# SELECT oid, relname FROM pg_class WHERE relname IN ('pers','appts','tasks');

PostgreSQL 通常会自动将未加引号的系统标识符（如表）转换为小写（即我可以 “CREATE TABLE foo” 和 “SELECT * FROM FOO” 仍然有效）。但是，在检查 pg_class 时，您必须以区分大小写的形式工作，或者使用不区分大小写的运算符。

此查询返回 

oid 	relname
9100 	pers
9110 	tasks
9120 	appts

(3 rows)

您的 OID 将不同。不要担心，但请注意它们是什么。

现在，如果我们在 pg_trigger 中查找，我们可以找到表使用的触发器。

  1. SELECT * from pg_trigger WHERE tgrelid in (9100, 9110, 9120);

请注意，此命令的输出表太宽，无法放入此 HTML 页面中。您可以在此处找到它。

pg_trigger 中的列是 

tgrelid
触发器所在的表。插入/更新/删除并调用触发器的表。

tgname
触发器的名称。触发器名称由PostgreSQL的参照完整性功能生成，除了要唯一之外，它们并不重要。

tgfoid
要调用的函数的OID。这是一列重要的列；通过更改调用的函数，我们可以更改执行的操作（CASCADE等）。见下文。

tgtype
这是什么类型的触发器？UPDATE、DELETE等。

tgenabled
此触发器是否已启用？

tgisconstraint
此触发器是否是约束的一部分？非参照完整性触发器（由CREATE TRIGGER用户声明）对于此可能为FALSE，但参照完整性触发器将始终为true。

tgconstrname
调用此触发器的约束的名称。如果您为约束命名（就像我们一样，例如pers__ref_tasks），那么这将是约束名称，否则它将是未命名的。

tgconstrrelid
设置约束的表的OID。

tgdeferrable
此约束是否可以延迟？等同于DEFERRABLE。见上文。

tginitdeferred
此约束是否最初延迟？等同于INITIALLY DEFERRED。见上文。

tgnargs
参照完整性函数的参数数量。从PostgreSQL 7.1开始，这似乎始终为6，不应该被编辑。

tgattr
不确定

tgargs
参照完整性函数的实际参数。

为了使它更容易理解和使用，视图很有帮助。

CREATE VIEW dev_ri
AS
SELECT t.oid as trigoid,
    c.relname as trig_tbl,
    t.tgfoid,
    f.proname as trigfunc,
    t.tgenabled,
    t.tgconstrname,
    c2.relname as const_tbl,
    t.tgdeferrable,
    t.tginitdeferred
    FROM pg_trigger t,
    pg_class c,
    pg_class c2,
    pg_proc f
    WHERE t.tgrelid=c.oid
    AND t.tgconstrrelid=c2.oid
    AND tgfoid=f.oid
    AND tgname ~ '^RI_'
    ORDER BY t.oid;

此视图需要PostgreSQL 7.1，因为使用了ORDER BY语句。对于早于7.1的PostgreSQL版本，您应该删除ORDER BY语句。

# select * from dev_ri;

请注意，此命令的输出表太宽，无法放入此 HTML 页面中。您可以在此处找到它。

现在，更容易理解正在发生的事情。例如，从触发器oid=263753，我们可以看到对'tasks'表的插入，调用了RI_FKey_check_ins，它检查了'pers'表。

从这里，我们可以

1）暂时禁用触发器。如果你想加载大量数据，并且不想因为参照完整性检查而减慢速度（并且不需要处理事务级延迟）

UPDATE pg_trigger SET tgenabled=FALSE WHERE oid=xxx;

从我们的视图中获取trigoid列，以找到要影响的触发器。

请注意，pg_dump 文件会这样做，以便可以按任意顺序插入表数据，而不必担心参照完整性规则。

2）如果一个触发器最初不是可延迟的，则将其设置为可延迟的，反之亦然。

UPDATE pg_trigger SET tgdeferrable=[ TRUE | FALSE ] WHERE oid=xxx;

或者，使触发器INITIALLY DEFERRED（或将其关闭）

UPDATE pg_trigger SET tginitdeferred=[ TRUE | FALSE ] WHERE oid=xxx;

3）更改触发器的操作。如果您创建了一个带有操作的触发器（或带有默认的 NO ACTION 操作），您可以通过更改调用的函数来改变主意。要获取所有参照完整性触发器函数的列表

      SELECT oid, proname FROM pg_proc where proname ~ '^RI_';

      oid 	proname
      1646 	RI_FKey_cascade_del
      1647 	RI_FKey_cascade_upd
      1644 	RI_FKey_check_ins
      1645 	RI_FKey_check_upd
      1654 	RI_FKey_noaction_del
      1655 	RI_FKey_noaction_upd
      1648 	RI_FKey_restrict_del
      1649 	RI_FKey_restrict_upd
      1652 	RI_FKey_setdefault_del
      1653 	RI_FKey_setdefault_upd
      1650 	RI_FKey_setnull_del
      1651 	RI_FKey_setnull_upd

您的OID可能不同。注意并使用您自己的。

在我们的示例中，将pers(pid)上的更新设置为CASCADE，而不是pers-tasks上的NO ACTION

UPDATE pg_trigger SET tgfoid=1647 WHERE oid=xxx;

其中xxx是当前pers-tasks的noaction_upd触发器

请注意，这些更改通常需要新的后端。退出并重新启动psql，或者重置您的客户端连接，您应该能够测试您的新设置。

检索自"https://wiki.postgresql.ac.cn/index.php?title=Referential_Integrity_Tutorial_%26_Hacking_the_Referential_Integrity_tables&oldid=17355"