PostgreSQL关联删除|极客教程

PostgreSQL关联删除

在 PostgreSQL 中，我们经常需要处理多个表之间的关联关系，并且在删除主表中的记录时，需要同时删除相关联的从表中的记录，以确保数据完整性和一致性。这涉及到外键约束和关联删除的问题，本文将详细介绍 PostgreSQL 中的关联删除操作。

外键约束

在 PostgreSQL 中，外键约束是用来确保两个表之间数据的一致性和完整性的一种方法。外键约束指定了一个表的某个列是另一个表的主键列或唯一键列的引用，这样在插入或更新数据时，会自动检查并保证数据的正确性。

在创建外键约束时，可以选择指定 ON DELETE 子句，用来指定在主表删除记录时从表的处理方式，包括 CASCADE、SET NULL、SET DEFAULT 和 RESTRICT。其中，CASCADE 是最常用的方式，表示当主表删除记录时，从表中相关联的记录也会被删除。

CASCADE 操作示例

假设有两个表 department 和 employee，它们之间是一对多的关系，即一个部门可以有多个员工。在创建表时，我们可以使用以下 SQL 语句定义外键约束，并指定 ON DELETE CASCADE：

CREATE TABLE department (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT REFERENCES department(id) ON DELETE CASCADE
);

在这个示例中，当我们删除部门时，相关联的员工记录也会被自动删除。下面我们来演示这个操作：

-- 插入数据
INSERT INTO department (name) VALUES ('HR');
INSERT INTO employee (name, department_id) VALUES ('Alice', 1), ('Bob', 1);

-- 查看员工表数据
SELECT * FROM employee;

-- 删除部门记录
DELETE FROM department WHERE id = 1;

-- 再次查看员工表数据
SELECT * FROM employee;

操作结果如下：

id | name
---|------
 1 | Alice
 2 | Bob

id | name
---|------
(0 rows)

可以看到，当我们删除部门记录时，相关联的员工记录也被自动删除了。

SET NULL 操作示例

另一种常用的操作是 SET NULL，表示当主表删除记录时，从表中的外键列会被设置为 NULL。这在某些情况下可能更合适，下面是一个示例：

CREATE TABLE department (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT REFERENCES department(id) ON DELETE SET NULL
);

-- 插入数据
INSERT INTO department (name) VALUES ('IT');
INSERT INTO employee (name, department_id) VALUES ('Charlie', 1);

-- 查看员工表数据
SELECT * FROM employee;

-- 删除部门记录
DELETE FROM department WHERE id = 1;

-- 再次查看员工表数据
SELECT * FROM employee;

操作结果如下：

id | name
---|------
 1 | Charlie

id | name | department_id
---|------|--------------
 1 | Charlie | NULL

可以看到，当我们删除部门记录时，员工表中的 department_id 被设置为 NULL。

SET DEFAULT 操作示例

SET DEFAULT 操作表示当主表删除记录时，从表中的外键列会被设置为默认值。这个操作需要事先定义外键列的默认值，下面是一个示例：

CREATE TABLE department (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT DEFAULT 1 REFERENCES department(id) ON DELETE SET DEFAULT
);

-- 插入数据
INSERT INTO department (name) VALUES ('Sales');
INSERT INTO employee (name) VALUES ('David');

-- 查看员工表数据
SELECT * FROM employee;

-- 删除部门记录
DELETE FROM department WHERE id = 1;

-- 再次查看员工表数据
SELECT * FROM employee;

操作结果如下：

id | name
---|------
 1 | David

id | name | department_id
---|------|--------------
 1 | David | 1

可以看到，当我们删除部门记录时，员工表中的 department_id 被设置为默认值 1。

RESTRICT 操作示例

RESTRICT 操作表示当主表删除记录时，如果从表中存在关联记录，会阻止主表记录的删除操作。这是一种极端的处理方式，用来确保数据的完整性，下面是一个示例：

CREATE TABLE department (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT REFERENCES department(id) ON DELETE RESTRICT
);

-- 插入数据
INSERT INTO department (name) VALUES ('Marketing');
INSERT INTO employee (name, department_id) VALUES ('Emma', 1);

-- 尝试删除部门记录
DELETE FROM department WHERE id = 1;

在这种情况下，如果我们尝试删除部门记录，将会收到一个错误提示，因为员工表中还有关联记录存在。