提要:在本教程中,你将学到外键约束及其应用。

外键是一个表中,用于唯一标识另一个表中的行的一个字段或一组字段。换句话说,外键定义了引用其它表的主键

包含外键的表称为引用表或子表。而外键引用的表称为被引用表或父表。

一个表可以有多个外键,这取决于它与其他表的关系。

在 PostgreSQL 中,通过外键约束来定义外键。外键约束指示子表中的一个字段或一组字段中的值与父表中的一个字段或一组字段中的值匹配。我们说外键约束维护子表和父表之间的参照完整性。

定义简单的外键约束

有一张 so_headers 表,存储了商店销售订单的头部信息,比如:销售订单的id、客户id 和送货地址:

CREATE TABLE so_headers (
 id serial PRIMARY KEY,
 customer_id int8,
 ship_to VARCHAR (255)
);

销售订单的每项数据存储在 so_items 表:

CREATE TABLE so_items (
  item_id int4 NOT NULL, 
  so_id int4,
  product_id int4,
  qty int4,
  net_price numeric,
  PRIMARY KEY (item_id,so_id)
);

so_items 的主键包含两个字段:item_iditem_id

foreign-key

为确保 so_items 表中包含的数据在 so_headers 都存在,我们需要定义一个外键:

CREATE TABLE so_items (
  item_id int4 NOT NULL, 
  so_id int4 REFERENCES so_headers(id),
  product_id int4,
  qty int4,
  net_price numeric,
  PRIMARY KEY (item_id,so_id)
);

注意,我们使用 REFERENCES 来定义外键。这个外键的意思是,so_itemsso_id 字段的值引用自 so_headers 表的 id 字段。

foreign-key

我们还可以通过表级约束来定义外键:

CREATE TABLE so_items (
 item_id int4 NOT NULL,
 so_id int4,
 product_id int4,
 qty int4,
 net_price numeric,
 PRIMARY KEY (item_id, so_id),
 FOREIGN KEY (so_id) REFERENCES so_headers (ID)
);

由于我们没有显式地指定外键的名字,PostgreSQL 默认将使用 table_column_fkey 格式对外键进行自动命名。上例中,PostgreSQL 自动将外键约束命名为 so_items_so_id_fkey

so_items 每一行都属于 so_headers 中对应的记录。so_headers 中每一行数据,可以在 so_items 里有一到多条对应记录。这种关系称为一对多关系。我们不能在 so_items 表的 so_id 字段中插入 so_headers 中不存在的 id 字段的记录。

so_headers 的某条存在于 so_items 的记录被删除时,会发生什么? PostgreSQL 提供这几个主要选项:DELETE RESTRICT, DELETE CASCADENO ACTION

PostgreSQL 不允许删除任何被其它表引用的记录。直到 so_items 中的所有引用行被删除,PostgreSQL 才会删除 so_headers表中的行。我们可以在定义外键的时候,使用 DELETE RESTRICT 来达到这一目标。

CREATE TABLE so_items (
  item_id int4 NOT NULL, 
  so_id int4 REFERENCES so_headers(id) ON DELETE RESTRICT,
  product_id int4,
  qty int4,
  net_price numeric,
  PRIMARY KEY (item_id,so_id)
);

如果要让 PostgreSQL 在删除某条记录的同时,将所有引用该条记录的关联记录也一起删除,我们可以使用 DELETE CASCADE

CREATE TABLE so_items (
  item_id int4 NOT NULL, 
  so_id int4 REFERENCES so_headers(id) ON DELETE CASCADE,
  product_id int4,
  qty int4,
  net_price numeric,
  PRIMARY KEY (item_id,so_id)
);

如果未指定 DELETE RESTRICTDELETE CASCADE,PostgreSQL 将使用默认的 NO ACTION。使用 NO ACTION时,如果在检查约束时引用行仍然存在,PostgreSQL 将抛出错误。

提示,这些 DELETE 的行为同样适用于 UPDATE, 也就是说,有 ON UPDATE RESTRICT, ON UPDATE CASCADEON UPDATE NO ACTION 等行为。

定义多个字段组成的外键

使用下面的语法可以定义多个字段组成的外键:

CREATE TABLE child_table(
  c1 integer PRIMARY KEY,
  c2 integer,
  c3 integer,
  FOREIGN KEY (c2, c3) REFERENCES parent_table (p1, p2)
);

给已存在的表定义外键

使用 ALTER TABLE 给一个已存在的表定义外键:

ALTER TABLE child_table 
ADD CONSTRAINT constraint_name FOREIGN KEY (c1) REFERENCES parent_table (p1);

最后一个注意事项是,如果你要给一个已存在的表添加 ON DELETE CASCADE 的外键约束,需要如下步骤:

  1. 删除已存在的外键约束
  2. 添加一个 ON DELETE CASCADE 的外键约束
ALTER TABLE child_table
DROP CONSTRAINT constraint_fkey;
ALTER TABLE child_table
ADD CONSTRAINT constraint_fk
FOREIGN KEY (c1)
REFERENCES parent_table(p1)
ON DELETE CASCADE;
下一篇:PostgreSQL 主键