PostgreSQL教程-数据定义-外键约束

时间：2021-10-09 来源：

5.4.5. 外键约束

一个外键约束指定一列(或一组列)中的值必须匹配出现在另一个表中某些行的值。我们说这维持了两个关联表之间的引用完整性。

例如我们有一个使用过多次的产品表：

CREATE TABLE products (

product_no integer PRIMARY KEY,

name text,

price numeric

);

让我们假设我们还有一个存储这些产品订单的表。我们希望保证订单表中只包含真正存在的产品的订单。因此我们在订单表中定义一个引用产品表的外键约束：

CREATE TABLE orders (

order_id integer PRIMARY KEY,

product_no integer REFERENCES products (product_no),

quantity integer

);

现在就不可能创建包含不存在于产品表中的product_no值(非空)的订单。

我们说在这种情况下，订单表是引用表而产品表是被引用表。相应地，也有引用和被引用列的说法。

我们也可以把上述命令简写为：

CREATE TABLE orders (

order_id integer PRIMARY KEY,

product_no integer REFERENCES products,

quantity integer

);

因为如果缺少列的列表，则被引用表的主键将被用作被引用列。

一个外键也可以约束和引用一组列。照例，它需要被写成表约束的形式。下面是一个例子：

CREATE TABLE t1 (

a integer PRIMARY KEY,

b integer,

c integer,

FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)

);

当然，被约束列的数量和类型应该匹配被引用列的数量和类型。

按照前面的方式，我们可以为一个外键约束命名。

一个表可以有超过一个的外键约束。这被用于实现表之间的多对多关系。例如我们有关于产品和订单的表，但我们现在希望一个订单能包含多种产品(这在上面的结构中是不允许的)。我们可以使用这种表结构：

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,

order_id integer REFERENCES orders,

quantity integer,

PRIMARY KEY (product_no, order_id)

);

注意在最后一个表中主键和外键之间有重叠。

我们知道外键不允许创建与任何产品都不相关的订单。但如果一个产品在一个引用它的订单创建之后被移除会发生什么?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)

);

限制删除或者级联删除是两种最常见的选项。RESTRICT阻止删除一个被引用的行。NO ACTION表示在约束被检察时如果有任何引用行存在，则会抛出一个错误，这是我们没有指定任何东西时的默认行为(这两种选择的本质不同在于NO ACTION允许检查被推迟到事务的最后，而RESTRICT则不会)。CASCADE指定当一个被引用行被删除后，引用它的行也应该被自动删除。还有其他两种选项：SET NULL和SET DEFAULT。这些将导致在被引用行被删除后，引用行中的引用列被置为空值或它们的默认值。注意这些并不会是我们免于遵守任何约束。例如，如果一个动作指定了SET DEFAULT，但是默认值不满足外键约束，操作将会失败。

与ON DELETE相似，同样有ON UPDATE可以用在一个被引用列被修改(更新)的情况，可选的动作相同。在这种情况下，CASCADE意味着被引用列的更新值应该被复制到引用行中。

正常情况下，如果一个引用行的任意一个引用列都为空，则它不需要满足外键约束。如果在外键定义中加入了MATCH FULL，一个引用行只有在它的所有引用列为空时才不需要满足外键约束(因此空和非空值的混合肯定会导致MATCH FULL约束失败)。如果不希望引用行能够避开外键约束，将引用行声明为NOT NULL。

一个外键所引用的列必须是一个主键或者被唯一约束所限制。这意味着被引用列总是拥有一个索引(位于主键或唯一约束之下的索引)，因此在其上进行的一个引用行是否匹配的检查将会很高效。由于从被引用表中DELETE一行或者UPDATE一个被引用列将要求对引用表进行扫描以得到匹配旧值的行，在引用列上建立合适的索引也会大有益处。由于这种做法并不是必须的，而且创建索引也有很多种选择，所以外键约束的定义并不会自动在引用列上创建索引。

更多关于更新和删除数据的信息请见第 6 章。外键约束的语法描述请参考CREATE TABLE。