Postgresql 中文操作指南

5.14. Dependency Tracking #

当涉及许多具有外键约束、视图、触发器、函数等的表的复杂数据库结构时，将隐式创建对象之间的依赖网络。例如，具有外键约束的表依赖于它引用的表。

When you create complex database structures involving many tables with foreign key constraints, views, triggers, functions, etc. you implicitly create a net of dependencies between the objects. For instance, a table with a foreign key constraint depends on the table it references.

为了确保整个数据库结构的完整性，PostgreSQL 确保你无法删除其他对象仍然依赖的对象。例如，尝试删除我们考虑在 Section 5.4.5 中的产品表，该表依赖于订单表，这将导致出现类似以下内容的错误消息：

To ensure the integrity of the entire database structure, PostgreSQL makes sure that you cannot drop objects that other objects still depend on. For example, attempting to drop the products table we considered in Section 5.4.5, with the orders table depending on it, would result in an error message like this:

DROP TABLE products;

ERROR:  cannot drop table products because other objects depend on it
DETAIL:  constraint orders_product_no_fkey on table orders depends on table products
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

错误消息包含一个有用的提示：如果不希望费心逐个删除所有依赖对象，则可以运行：

The error message contains a useful hint: if you do not want to bother deleting all the dependent objects individually, you can run:

DROP TABLE products CASCADE;

以及所有依赖对象都将被删除，依赖于它们的任何对象也会递归地被删除。在这种情况下，它不会删除订单表，它只会删除外键约束。它在那里停止是因为没有任何内容依赖于外键约束。（如果你想检查 DROP …​ CASCADE 将做什么，请在没有 CASCADE 的情况下运行 DROP，并读取 DETAIL 输出。）

and all the dependent objects will be removed, as will any objects that depend on them, recursively. In this case, it doesn’t remove the orders table, it only removes the foreign key constraint. It stops there because nothing depends on the foreign key constraint. (If you want to check what DROP …​ CASCADE will do, run DROP without CASCADE and read the DETAIL output.)

PostgreSQL 中几乎所有 DROP 命令都支持指定 CASCADE。当然，可能的依赖项的性质会因对象类型而异。你还可以编写 RESTRICT 而不是 CASCADE，以获取默认行为，即阻止删除任何其他对象依赖的对象。

Almost all DROP commands in PostgreSQL support specifying CASCADE. Of course, the nature of the possible dependencies varies with the type of the object. You can also write RESTRICT instead of CASCADE to get the default behavior, which is to prevent dropping objects that any other objects depend on.

Note

根据 SQL 标准，DROP 命令中需要指定 RESTRICT 或 CASCADE。没有任何数据库系统实际执行此规则，但默认行为是 RESTRICT 还是 CASCADE 则因系统而异。

According to the SQL standard, specifying either RESTRICT or CASCADE is required in a DROP command. No database system actually enforces that rule, but whether the default behavior is RESTRICT or CASCADE varies across systems.

如果 DROP 命令列出了多个对象，则仅当组外存在依赖项时才需要 CASCADE。例如，在说明 DROP TABLE tab1, tab2 中，外键引用 tab1 从 tab2 的存在并不意味着需要 CASCADE 才能成功。

If a DROP command lists multiple objects, CASCADE is only required when there are dependencies outside the specified group. For example, when saying DROP TABLE tab1, tab2 the existence of a foreign key referencing tab1 from tab2 would not mean that CASCADE is needed to succeed.

对于将主体定义为字符串文字的用户定义函数或过程，PostgreSQL 会跟踪与函数外部可见属性（如其参数和结果类型）关联的依赖项，但 not 依赖项只能通过检查函数主体得知。以这种情况为例：

For a user-defined function or procedure whose body is defined as a string literal, PostgreSQL tracks dependencies associated with the function’s externally-visible properties, such as its argument and result types, but not dependencies that could only be known by examining the function body. As an example, consider this situation:

CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow',
                             'green', 'blue', 'purple');

CREATE TABLE my_colors (color rainbow, note text);

CREATE FUNCTION get_color_note (rainbow) RETURNS text AS
  'SELECT note FROM my_colors WHERE color = $1'
  LANGUAGE SQL;

（请参阅 Section 38.5 了解 SQL 语言函数的说明。）PostgreSQL 将意识到 get_color_note 函数依赖于 rainbow 类型：删除该类型将强制删除该函数，因为其参数类型将不再被定义。但 PostgreSQL 不会认为 get_color_note 依赖于 my_colors 表，因此不会在删除表的时候删除该函数。尽管这种方法有一些缺点，但也有好处。从某种意义上说，即使表丢失了，函数仍然有效，尽管执行它会导致错误；创建具有相同名称的新表将允许函数再次工作。

(See Section 38.5 for an explanation of SQL-language functions.) PostgreSQL will be aware that the get_color_note function depends on the rainbow type: dropping the type would force dropping the function, because its argument type would no longer be defined. But PostgreSQL will not consider get_color_note to depend on the my_colors table, and so will not drop the function if the table is dropped. While there are disadvantages to this approach, there are also benefits. The function is still valid in some sense if the table is missing, though executing it would cause an error; creating a new table of the same name would allow the function to work again.

另一方面，对于主体以 SQL 标准样式编写的 SQL 语言函数或过程，解析器在函数定义时解析主体，并存储解析器识别的所有依赖关系。因此，如果我们以以下方式编写上述函数：

On the other hand, for a SQL-language function or procedure whose body is written in SQL-standard style, the body is parsed at function definition time and all dependencies recognized by the parser are stored. Thus, if we write the function above as

CREATE FUNCTION get_color_note (rainbow) RETURNS text
BEGIN ATOMIC
  SELECT note FROM my_colors WHERE color = $1;
END;

那么 DROP 将了解和强制执行函数对 my_colors 表的依赖性。

then the function’s dependency on the my_colors table will be known and enforced by DROP.