PostgreSQL 外键

<-上一节 下一节->

PostgreSQL 外键约束

在 PostgreSQL 中，外键是表中的一个或多个列，它指向同一个或另一个表中的 主键 或 唯一键 列。

外键在父表和子表之间建立参照完整性，具有外键的表称为子表，具有被外键引用的主键或唯一键列的表称为父表。

例如，下表employee有一个外键列dept_id链接到表中的 主键 列，因此，它在department和employee表之间形成一对多关系，这意味着对于一个部门，可能有多个员工。换句话说， employee表中的多个记录可以包含指向department表中dept_id相同的值。

创建表时定义外键

您可以在使用 CREATE TABLE 语句 创建表时定义外键。

语法：外键约束
CONSTRAINT [constraint_name] FOREIGN KEY(fk_columns) 
REFERENCES <parent_table>(parent_table_columns)
[ON DELETE <delete_action>]
[ON UPDATE <update_action]

在上面的语法中，

  • 使用 CONSTRAINT 关键字来定义约束，然后是外键约束的名称，约束名称是可选的；如果您不指定它，PostgreSQL 将按照默认命名约定给出名称。
  • 在关键字FOREIGN KEY后指定要定义外键约束的表的一个或多个列名。
  • REFERENCES 关键字用于指定当前表中外键引用的父表和父表列。
  • ON DELETE 和 ON UPDATE 子句是可选的 。这些操作决定了在父表中删除或更新 主键 时的行为。

PostgreSQL 支持以下引用操作：

  1. NO ACTION
  2. RESTRICT
  3. SET NULL
  4. SET DEFAULT
  5. CASCADE

以下示例演示了在 employee 表中创建指向表 department 的外键。

示例: 定义外键
CREATE TABLE department(
    dept_id	INT PRIMARY KEY,
    dept_name	VARCHAR(50)
);

CREATE TABLE employee(
    emp_id 	INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email	VARCHAR (100),
    hiredate	DATE,
    manager_id	INT,
    salary		INT,
    dept_id		INT,

    CONSTRAINT FK_employee_department FOREIGN KEY(dept_id)
        REFERENCES department(dept_id)
);

在上面的例子中， employee 表中的 dept_id 列被定义为引用 department 表的主键列 dept_id 的外键列。 CONSTRAINT FK_employee_department 指定外键名 FK_employee_department ， FOREIGN KEY(dept_id) 指定 employee 表中的外键列， REFERENCES department(dept_id) 指定外键列指的是 department 表的 dept_id 列。

上面的外键在部门表department和员工表employee之间建立了一对多的关系，一个部门可以有零个或多个员工，一个员工不能有多个部门。

请注意，我们没有定义任何操作，例如 ON DELETE 或 ON UPDATE 子句，所以，它默认是 NO ACTION。

注意 ：外键列名称不需要与 主键 列名称相同，但出于可读性目的，建议这样做。

NO ACTION – 在删除或更新时引发错误

如果未指定 ON DELETE 或 ON UPDATE 子句，则 NO ACTION 引用操作是默认操作。NO ACTION 产生一个错误，表明删除或更新会造成外键约束冲突。

以下示例演示了 NO ACTION 引用操作。

示例: 插入数据
INSERT INTO department 
VALUES(1,'HR'),
	(2,'IT'),
	(3,'FINANCE');

INSERT INTO employee(emp_id, first_name, last_name, dept_id) 
VALUES(1,'Annie','Smith',1),
	(2,'Susan','Klassen',1),
	(3,'May','Kaasman',2);

现在让我们删除 dept_id = 1 的部门，如下所示。

我们试图删除 department 表中 dept_id = 1 的行，但 employee 表中有两个员工属于该部门。因此，PostgreSQL 会引发外键约束违规错误，并且不允许删除该部门。

要删除department表中的一行，需要先删除属于该部门的所有员工，然后再删除一个部门。

RESTRICT

RESTRICT 操作与 NO ACTION 相同，区别在于当您将外键约束定义为INITIALLY DEFERRED或INITIALLY IMMEDIATE的延迟时。

SET NULL - 将引用列设置为 NULL

当使用 ON DELETE SET NULL 或 ON UPDATE SET NULL 创建外键时，在删除或更新父表中的数据时，子表中引用行的外键列将自动设置为 NULL。

以下示例演示了 SET NULL 操作。

示例: SET NULL
CREATE TABLE department(
    dept_id	INT PRIMARY KEY,
    dept_name	VARCHAR(50)
);

CREATE TABLE employee(
    emp_id 	INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email_id	VARCHAR (100),
    hire_date	DATE,
    manager_id	INT,
    salary		INT,
    dept_id		INT,
    
    CONSTRAINT FK_Employee_Department FOREIGN KEY(dept_id)
        REFERENCES department(dept_id)
        ON DELETE SET NULL
);

现在，让我们将数据插入到上面的表中。

示例: 插入数据
INSERT INTO department 
VALUES(1,'HR'),
	(2,'IT'),
	(3,'Finance');

INSERT INTO employee(emp_id, first_name, last_name, dept_id) 
VALUES(1,'Annie','Smith',1),
	(2,'Susan','Klassen',1),
	(3,'May','Kaasman',2);

现在尝试删除 dept_id = 1 的部门，如下所示。

我们用 ON DELETE SET NULL 子句定义了外键约束，因此 employee 表中 dept_id 为 1 的两个引用行现在设置为 NULL，现在我们来看一下employee表中的数据。

SET DEFAULT

当使用 ON DELETE SET DEFAUL T 或 ON UPDATE SET DEFAULT 创建外键时，在删除或更新父表中的数据时，子表中引用行的外键列将自动设置为默认值，如果默认值不为空，则引用表中必须有一行与默认值匹配，否则操作将失败。

以下示例演示了 SET DEFAULT 操作：

示例: SET DEFAULT 
CREATE TABLE department(
    dept_id	INT PRIMARY KEY,
    dept_name	VARCHAR(50)
);

CREATE TABLE employee(
    emp_id 	INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email_id	VARCHAR (100),
    hire_date	DATE,
    manager_id	INT,
    salary		INT,
    dept_id		INT DEFAULT 3,
    CONSTRAINT FK_Employee_Department FOREIGN KEY(dept_id)
        REFERENCES department(dept_id)
        ON DELETE SET DEFAULT
);

将数据插入上表：

示例: Insert Data
INSERT INTO department 
VALUES(1,'HR'),
	(2,'IT'),
	(3, 'Finance');

INSERT INTO employee(emp_id, first_name, last_name, dept_id) 
VALUES(1,'Annie','Smith',1),
	(2,'Susan','Klassen',2),
	(3,'May','Kaasman',2);

现在尝试删除 dept_id = 1 的部门，如下所示。

DELETE FROM department WHERE dept_id = 1;

如上，它允许删除department，我们用 ON DELETE SET DEFAULT 定义了外键约束，所以在 employee 表中 dept_id 为 1 的引用行 emp_id = 1 现在被设置为 DEFAULT 值，即 3。让我们看一下 employee 表中的数据。

请注意，在 employee 表中为 dept_id 列指定了默认值 3。如果 employee 表中的 dept_id 没有指定默认值，那么上面的删除会将值设置为NULL。

dept_id 3必须存在于 department 表中，否则将引发错误。例如，如果您在 employee 表中指定 4 作为 dept_id 的默认值，那么尝试删除 department 表中的一行会引发错误，如下所示。

CASCADE

当使用 ON DELETE CASCADE 或 ON UPDATE CASCADE 创建外键时，在父表中删除或更新引用行时，子表中引用行的外键行将自动删除。

以下示例演示了 CASCADE 操作：

示例: ON DELETE CASCADE
CREATE TABLE department(
    dept_id	INT PRIMARY KEY,
    dept_name	VARCHAR(50)
);

CREATE TABLE employee(
    emp_id 	INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email_id	VARCHAR (100),
    hire_date	DATE,
    manager_id	INT,
    salary		INT,
    dept_id		INT,

    CONSTRAINT FK_Employee_Department FOREIGN KEY(dept_id)
        REFERENCES department(dept_id)
        ON DELETE CASCADE
);

现在，将数据插入表中。

示例: 数据插入
INSERT INTO department 
VALUES(1,'HR'),
	(2,'IT'),
	(3, 'Finance');

INSERT INTO employee(emp_id, first_name, last_name, dept_id) 
VALUES(1,'Annie','Smith',1),
	(2,'Susan','Klassen',2),
	(3,'May','Kaasman',2);

现在尝试删除 dept_id = 1 的部门。

DELETE FROM department WHERE dept_id = 1;

上面的 DELETE 语句 执行成功，允许删除 department 表中的一行。由于 ON DELETE CASCADE 选项，员工表employee中的所有引用行都将被删除，现在我们看一下员工表employee中的数据。

如上，有一个 emp_id = 1 的员工属于'HR'部门，删除 emp_id = 1 的'HR'部门时，将从员工表employee中删除。

向现有表添加外键

可以将外键约束添加到现有表的一个或多个列，如果要添加外键约束的表包含数据，则该列或列集必须具有与父表的引用列匹配的值，否则将不允许添加约束。

语法：ALTER TABLE 添加约束
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> FOREIGN KEY(<fk_columns>)
    REFERENCES <parent_table>(<parent_table_columns>)
    [ON DELETE <action>]
    [ON UPDATE <action>];

假设我们有 department 和 employee 表，如下所示，它们之间没有定义任何父子关系。

请注意，一名员工不属于 dept_id = NULL 的任何部门，现在我们将在 employee 表的 dept_id 列上添加一个外键约束。

示例: 添加外键
ALTER TABLE employee
    ADD CONSTRAINT FK_Employee_Department FOREIGN KEY(dept_id)
    REFERENCES department(dept_id)
    ON DELETE CASCADE;

以上示例将在现有员工表中创建一个外键。

<-上一节 下一节->
关于我们联系我们免责声明
备案号:苏ICP备2021040383号-2