【PostgreSQL】约束-外键_postgresql外键

【PostgreSQL】约束链接

检查
唯一
主键
外键
排他

外键

外键是关系数据库中的一个概念，用来建立表与表之间的关系。它是指一个表中的字段（或字段组合），用来关联另一个表的主键（或唯一标识字段）。通过外键，可以实现表之间的数据关联和一致性约束。

外键的作用主要有以下几个方面：

• 建立表与表之间的关系：通过外键，可以建立表与表之间的关联关系，实现数据的一致性和完整性。
• 约束数据的完整性：外键可以限制在一个表中插入、更新或删除数据的操作，确保表之间的数据一致性。
• 提高查询效率：外键可以加速数据的查询，通过关联查询可以快速获取相关数据。

外键在数据库设计和使用中有一些注意事项：

• 外键的字段类型和大小必须与关联表的主键字段类型和大小一致，以确保数据的正确关联。
• 外键字段要么为空，要么具有唯一性约束，以避免数据的重复关联。
• 外键字段要在关联表中有对应的索引，以提高查询效率。
• 外键的更新和删除操作需要慎重考虑，必须遵循数据库的一致性规则，避免数据的丢失或不一致。

总之，外键是关系数据库中用来建立表与表之间关联关系的重要工具，可以提高数据的一致性和查询效率。在数据库设计和使用中，需要正确理解和使用外键，以确保数据的完整性和正确性。

PostgreSQL 外键

PostgreSQL 外键是一种数据库约束，用于确保关系数据库中的数据完整性。外键定义了两个表之间的关系，其中一个表的列引用了另一个表的主键列。
外键约束指定一列（或一组列）中的值必须与另一个表的某一行中显示的值匹配。我们说这保持了两个相关表之间的引用完整性。

假设您有我们已经多次使用过的产品表：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
1
2
3
4
5

我们还假设您有一个存储这些产品订单的表。我们希望确保订单表仅包含实际存在的产品的订单。因此，我们在引用 products 表的 orders 表中定义了一个外键约束：

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);
1
2
3
4
5

现在，无法创建具有非 NULL 条目且未出现在产品表中的订单。product_no

我们说在这种情况下，订单表是引用表，产品表是引用表。同样，还有引用列和引用列。

您也可以将上述命令缩短为：

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);
1
2
3
4
5

因为在没有列列表的情况下，引用表的主键用作引用的列。

您可以按常规方式为外键约束指定自己的名称。

外键还可以约束和引用一组列。像往常一样，它需要以表约束形式编写。下面是一个人为的语法示例：

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
1
2
3
4
5
6

当然，约束列的数量和类型需要与引用列的数量和类型相匹配。

有时，外键约束的“其他表”是同一个表很有用;这称为自引用外键。例如，如果希望表的行表示树结构的节点，则可以将

CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);
1
2
3
4
5
6

顶级节点将具有 NULL ，而非 NULL 条目将被限制为引用表parent_idparent_id的有效行。

一个表可以有多个外键约束。这用于实现表之间的多对多关系。假设您有关于产品和订单的表，但现在您希望允许一个订单包含可能多个产品（上面的结构不允许）。您可以使用以下表结构：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
1
2
3
4
5

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);
1
2
3
4
5

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
1
2
3
4
5
6

请注意，主键与最后一个表中的外键重叠。

我们知道外键不允许创建与任何产品无关的订单。但是，如果在创建引用该产品的订单后删除了该产品，该怎么办？SQL也允许你处理它。直观地说，我们有几个选择：

• 禁止删除引用的产品
• 同时删除订单
• 别的？

为了说明这一点，让我们在上面的多对多关系示例中实施以下策略：当有人想要删除订单（通过）仍引用的产品时，我们不允许这样做。如果有人删除了订单，订单项目order_items也会被删除：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

限制和级联删除是两个最常见的选项。 防止删除引用的行。 表示如果在检查约束时仍存在任何引用行，则会引发错误;如果未指定任何内容，则这是默认行为。（这两种选择之间的本质区别在于，允许将支票推迟到交易的后期，而不允许。 指定在删除引用的行时，引用该行的行也应自动删除。还有另外两个选项：和 .这会导致在删除引用行时，引用行中的引用列分别设置为 null 或其默认值。请注意，这些并不能成为您遵守任何约束的借口。例如，如果某个操作指定了某个操作，但默认值不满足外键约束，则该操作将失败。

适当的操作选择取决于相关表所表示的对象类型。当引用表表示的内容是被引用表所表示内容的组成部分并且不能独立存在时，那么可能是合适的。如果两个表表示独立的对象，则 or 更合适;然后，实际想要删除这两个对象的应用程序必须明确这一点并运行两个删除命令。在上面的例子中，订单项目是订单的一部分，如果删除订单，则自动删除它们会很方便。但是产品和订单是不同的东西，因此自动删除产品会导致某些订单项目的删除可能会被认为是有问题的。如果外键关系表示可选信息，则操作 or 可能是适当的。例如，如果 products 表包含对产品经理的引用，并且产品经理条目被删除，则将产品的产品经理设置为 null 或默认值可能很有用。

操作和可以采用列列表来指定要设置的列。通常，设置了外键约束的所有列;在某些特殊情况下，仅设置子集很有用。请看以下示例：SET NULL SET DEFAULT

CREATE TABLE tenants (
    tenant_id integer PRIMARY KEY
);

CREATE TABLE users (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    user_id integer NOT NULL,
    PRIMARY KEY (tenant_id, user_id)
);

CREATE TABLE posts (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    post_id integer NOT NULL,
    author_id integer,
    PRIMARY KEY (tenant_id, post_id),
    FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

如果没有列的规范，外键tenant_id也会将该列设置为 null，但该列仍需要作为主键的一部分。

与ON DELETE类似，当引用的列被更改（ON UPDATE）时，也会调用SET NULL。可能的操作是相同的，只是不能为SET DEFAULT和CASCADE指定列列表。在这种情况下，意味着应将引用列的更新值复制到引用行中。

通常，如果引用行的任何引用列为 null，则引用行不需要满足外键约束。如果添加到外键声明中，则仅当引用行的所有引用列均为 null 时，引用行才会转义满足约束MATCH FULL（因此，可以保证 null 值和非 null 值的混合不会使约束失败）。如果不希望引用行能够避免满足外键约束，请将引用列声明为 。MATCH FULL NOT NULL

外键必须引用作为主键或形成唯一约束的列。这意味着引用的列始终具有索引（主键或唯一约束的基础索引）;因此，检查引用行是否具有匹配项将是有效的。由于引用表中的行或引用列的行需要扫描引用表以查找与旧值匹配的行，因此通常最好也为引用列编制索引。由于这并不总是必需的，并且有许多关于如何编制索引的选项，因此声明外键约束不会自动在引用列上创建索引。

示例

在 PostgreSQL 中，可以通过以下方式创建外键：

创建表时定义外键：

CREATE TABLE 表名 (
    列名 数据类型,
    ...
    外键列名 数据类型 REFERENCES 参考表名 (参考列名)
);

1
2
3
4
5
6

例如，创建一个 orders 表和一个 customers 表，使 orders 表的 customer_id 列引用 customers 表的 id 列：

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(20),
    customer_id INTEGER REFERENCES customers(id)
);

1
2
3
4
5
6
7
8
9
10
11

使用 ALTER TABLE 语句添加外键约束：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (外键列名) REFERENCES 参考表名 (参考列名);
1

例如，为 orders 表添加一个外键约束：

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);
1

当创建或修改表时，外键约束会检查数据的完整性，并确保外键列的值始终与参考表的主键列的值匹配。如果尝试插入或更新外键列的值，但该值在参考表中不存在，则操作将被拒绝。

外键可以帮助维护表之间的关系，并确保数据的一致性和完整性。