PostgreSQL 教程: 外键约束

九月 13, 2023

摘要：在本教程中，您将了解 PostgreSQL 外键以及如何使用外键约束向表添加外键。

目录

PostgreSQL 外键约束简介

外键是表中引用另一个表的主键的一列或一组列。

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

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

在 PostgreSQL 中，您可以使用外键约束来定义外键。外键约束有助于维护子表和父表之间数据的引用完整性。

外键约束表示子表中的一列或一组列中的值等于父表中的一列或一组列中的值。

PostgreSQL 外键约束语法

下面说明了外键约束语法：

[CONSTRAINT fk_name]
   FOREIGN KEY(fk_columns) 
   REFERENCES parent_table(parent_key_columns)
   [ON DELETE delete_action]
   [ON UPDATE update_action]

在这个语法中：

  • 首先，在CONSTRAINT关键字后指定外键约束的名称。CONSTRAINT子句是可选的。如果省略它，PostgreSQL 将分配一个自动生成的名称。
  • 其次，在FOREIGN KEY关键字后面的括号中指定一个或多个外键列。
  • 第三，在REFERENCES子句中指定外键列引用的父表和父键列。
  • 最后，在ON DELETE和ON UPDATE子句中指定删除和更新操作。

删除和更新操作决定了父表中的主键被删除和更新时的行为。由于主键很少更新，因此ON UPDATE action在实际中并不经常被用到。我们将重点关注ON DELETE action。

PostgreSQL 支持以下操作：

  • SET NULL
  • SET DEFAULT
  • RESTRICT
  • NO ACTION
  • CASCADE

PostgreSQL 外键约束示例

以下语句创建customers和contacts表：

DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS contacts;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
	  REFERENCES customers(customer_id)
);

在本例中，customers表是父表，contacts表是子表。

每个客户有零个或多个联系方式，每个联系方式属于零个或一个客户。

contacts表中的customer_id列是引用customers表中同名主键列的外键列。

contacts表中的以下外键约束fk_customer将customer_id定义为外键：

CONSTRAINT fk_customer
   FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)

由于该外键约束没有指定ON DELETE和ON UPDATE操作，因此它默认为NO ACTION。

NO ACTION

以下将数据插入到customers和contacts表中：

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');	   
	   
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev');

以下语句从customers表中删除 ID 为 1 的客户：

DELETE FROM customers
WHERE customer_id = 1;

由于ON DELETE NO ACTION，PostgreSQL 会发出约束冲突，因为contacts表中仍然存在客户 ID 为 1 的引用行：

ERROR:  update or delete on table "customers" violates foreign key constraint "fk_customer" on table "contacts"
DETAIL:  Key (customer_id)=(1) is still referenced from table "contacts".
SQL state: 23503

RESTRICT动作与NO ACTION类似。仅当您将外键约束定义为INITIALLY DEFERRED或INITIALLY IMMEDIATE模式的DEFERRABLE时，才会出现差异。我们将在后续教程中对此进行更多讨论。

SET NULL

当删除父表中引用的行时，SET NULL自动设置子表引用行中的外键列为NULL。

以下语句删除示例表，并使用ON DELETE SET NULL子句定义的外键，重新创建它们：

DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
	  REFERENCES customers(customer_id)
	  ON DELETE SET NULL
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');	   

INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev');

以下语句将数据插入到customers和contacts表中：

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');	   

INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev');

要了解SET NULL的工作原理，让我们从customers表中删除 ID 为 1 的客户：

DELETE FROM customers
WHERE customer_id = 1;

由于ON DELETE SET NULL操作，contacts表中的引用行设置为 NULL。以下语句显示contacts表中的数据：

SELECT * FROM contacts;

PostgreSQL Foreign Key - SET NULL

从输出中可以清楚地看到，customer_id为 1 的行现在的customer_id列值已设置成NULL。

CASCADE

当删除父表中的引用行时，ON DELETE CASCADE会自动删除子表中的所有引用行。在实践中，ON DELETE CASCADE是最常用的选项。

以下语句重新创建示例表。但是，外键约束fk_customer的删除操作更改为CASCADE：

DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
	  REFERENCES customers(customer_id)
	  ON DELETE CASCADE
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');	   
	   
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev');

以下语句删除 ID 为 1 的客户：

DELETE FROM customers
WHERE customer_id = 1;

由于ON DELETE CASCADE操作，contacts表中的所有引用行都会自动删除：

SELECT * FROM contacts;

PostgreSQL Foreign Key - ON DELETE CASCADE

SET DEFAULT

当从父表中删除引用行时，ON DELETE SET DEFAULT将子表中引用行的外键列设置为默认值。

向现有表添加外键约束

要将外键约束添加到现有表，请使用以下形式的 ALTER TABLE 语句：

ALTER TABLE child_table 
ADD CONSTRAINT constraint_name 
FOREIGN KEY (fk_columns) 
REFERENCES parent_table (parent_key_columns);

当您向现有表添加带ON DELETE CASCADE选项的外键约束时，需要执行以下步骤：

首先，删除现有的外键约束：

ALTER TABLE child_table
DROP CONSTRAINT constraint_fkey;

其次，使用 ON DELETE CASCADE操作添加新的外键约束：

ALTER TABLE child_table
ADD CONSTRAINT constraint_fk
FOREIGN KEY (fk_columns)
REFERENCES parent_table(parent_key_columns)
ON DELETE CASCADE;

在本教程中，您了解了 PostgreSQL 外键以及如何使用外键约束为表创建外键。

搜索

分类

标签