跳至主要内容

SQL ON DELETE RESTRICT:防止意外数据丢失

了解 SQL ON DELETE RESTRICT 如何保护数据完整性。学习它如何阻止不安全的删除,以及何时应优先于 CASCADE 或 SET NULL。
· 10分钟 阅读

使用 AI 探索

在 SQL 中删除数据通常不只是移除一行那么简单。在关系型数据库中,表之间往往有关联,在一个表中删除一条记录可能会影响另一个表。

SQL 提供了 ON DELETE 子句,用来定义当被引用的行被删除时应当发生什么。在 ON DELETE 子句后,您可以指定相应行为,例如阻止删除、自动删除依赖的行,或更新被引用的数据。

在本教程中,我将向您展示 ON DELETE RESTRICT 约束,它会在仍存在任何依赖的子行时阻止删除父行。我还将详细介绍该约束的工作原理、数据库为何要强制执行它,以及与其他删除规则相比何时是正确的选择。最后,如果您在寻找相反的行为——即删除父行时自动删除相关记录——请参阅我们的 SQL ON DELETE CASCADE 指南。

什么是 SQL 中的 ON DELETE RESTRICT?

SQL 中的 ON DELETE RESTRICT 约束是一种数据库安全机制。它确保当仍有“子”记录指向某个“父”记录时,您无法删除该父记录。

注意:虽然 PostgreSQL 和 MySQL 等数据库直接支持 RESTRICT 关键字,但 SQL Server 则通过 ON DELETE NO ACTION 来实现相同的限制行为。

参照完整性与外键

参照完整性指的是表之间的关系在任何时候都必须保持有效。外键通过要求子表中的值对应于父表中现有的行来强制实现这一点。例如,Employees 表中的 DepartmentID 可能引用 Departments 表中的某个特定 ID。

因此,SQL 的 DELETE 规则之所以存在,是因为移除父行可能会破坏这种关系。数据库需要明确的指令来决定是阻止删除、级联删除,还是对依赖行应用其他操作。

RESTRICT 的真实含义

当您使用 ON DELETE RESTRICT 定义外键时,数据库会在执行删除前检查是否存在相关的子行。如果存在子记录,删除操作会被自动拒绝。在这种情况下,RESTRICT 是阻止删除发生,而不是修改数据,稍后教程中我们会看到这一点。

SQL 中 ON DELETE RESTRICT 的工作原理

要理解 ON DELETE RESTRICT 约束如何工作,您需要看看数据库在一次事务中逐步评估删除操作的过程。

父表与子表关系

在 SQL 中,父表保存主记录,子表保存依赖记录。例如,您的数据库可能有一个父表 Categories,其中包含“Electronics”的 category_id。子表 Products 中会有“Smartphone”这条记录,并通过 category_id 关联到父表。

当您对父表执行 DELETE 命令时,数据库引擎会立即扫描所有相关子表,查看是否有行指向该特定父 ID。

当您尝试删除受限的行会发生什么?

如果您尝试删除仍被引用的父行,数据库会停止操作并抛出错误。该错误消息表明违反了外键约束。

这种行为是有意为之,它迫使您做出明确决定:要么先删除或更新依赖的子行,要么重新考虑是否应删除该父行。

SQL 中的 ON DELETE RESTRICT 与其他删除规则的比较

在 DBMS 中,有特定规则来管理如何从相关表中删除数据。本节将把 ON DELETE RESTRICT 约束与 SQL 中常见的删除规则进行比较,便于您清楚了解每种规则对相关数据的影响。

SQL 中的 RESTRICT 与 CASCADE

SQL 的 RESTRICT 约束会在子行引用父行时阻止删除父行,从而确保不会意外丢失数据。以下查询展示了在定义表模式时如何实现 RESTRICT。它会在存在引用该客户的任何订单时,阻止删除该客户。

-- Parent table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Child table with ON DELETE RESTRICT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);

然而,SQL 的 CASCADE 约束会在删除时自动删除所有依赖的子行。在下面的示例中,CASCADE 允许在客户记录被删除时,自动删除与该客户相关的所有订单。

-- Child table with ON DELETE CASCADE
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

RESTRICT 与 SET NULL、SET DEFAULT

SQL 的 SET NULL 和 SET DEFAULT 约束会保留子行,但改变它们对父行的引用方式。

例如,在下面的查询中,当客户被删除时,相关订单中的 customer_id 会被设置为 NULL,从而保留订单记录但移除与客户的关联。

-- Child table using SET NULL
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
);

如果您使用 SET DEFAULT 约束,当客户被删除时,相关订单中的 customer_id 会被设置为其默认值,从而在保留订单记录的同时,为其分配一个预定义的占位值。

-- Child table using SET DEFAULT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT DEFAULT 0,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET DEFAULT
);

在 SQL 中使用 ON DELETE RESTRICT 创建外键

要在 SQL 中实现 ON DELETE RESTRICT 约束,您需要在创建两个表之间的关系时进行定义。

在表模式中定义 RESTRICT

您可以在创建表时指定删除行为,或者如果表已存在,也可以在之后添加。

下面的示例展示了在 SQL 的 CREATE TABLE 语句中定义新表时,如何使用 ON DELETE RESTRICT 外键约束。

-- Stores department details
CREATE TABLE departments (
    department_id INT PRIMARY KEY, 
    department_name VARCHAR(100)
);

-- -- Prevents deleting a department with employees
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,      -- Unique employee identifier
    department_id INT, 
    CONSTRAINT fk_employees_departments
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE RESTRICT 
);

下面的查询通过 SQL 的 ALTER TABLE 语句,将相同的外键规则添加到已存在的 employees 表上,以防止删除仍被员工引用的部门。

-- Adds a foreign key constraint after the table already exists
ALTER TABLE employees
ADD CONSTRAINT fk_employees_departments
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE RESTRICT;    -- Blocks deletion of departments that have employees

SQL 中的默认删除行为

在许多常见的数据库系统(如 SQL Server、PostgreSQL 和 MySQL)中,RESTRICT 是默认行为。如果您定义外键时未指定任何 ON DELETE 子句,数据库通常会按您写了 ON DELETE NO ACTION 或 ON DELETE RESTRICT 来处理。这样的引擎设计可确保您收到错误,而不是意外删除大量相关数据。

SQL 中 ON DELETE RESTRICT 的常见用例

在实际场景中,当数据完整性要求在删除前进行明确把控时,通常会应用 SQL 的 ON DELETE RESTRICT 约束。

保护关键参考数据

SQL 的 RESTRICT 约束常用于表示核心参考数据的表,如用户、账户、产品或查找值等。这些记录通常被系统的多个部分共享,自动删除它们可能会导致广泛的数据丢失或不一致。

例如,删除仍然拥有订单、审计日志或权限的用户通常是不安全的。借助 SQL 的 ON DELETE RESTRICT 约束,数据库会阻止删除,并迫使您先解决这些依赖关系,从而维护历史数据和交易数据的完整性。

强制执行明确的清理流程

当您希望清理过程具有意图性且可追踪时,SQL 的 RESTRICT 约束也非常有用。它使开发人员和管理员更慎重地处理数据。与其让数据库自动删除记录,RESTRICT 约束会迫使您审查依赖数据,以决定是删除子记录、将其重新分配给新的父记录,还是取消操作。

排查因 SQL 中 RESTRICT 导致的 DELETE 错误

当 ON DELETE RESTRICT 约束阻止删除时,如果您没有预料到,所产生的错误可能会令人困惑。理解这些错误有助于您安全地解决问题。

理解约束违规错误

当您尝试删除仍被引用的父行时,数据库会抛出外键约束违规。以下是在不同数据库系统中的典型错误消息:

  • PostgreSQL:ERROR: update or delete on table "parent_table" violates foreign key constraint "fk_name" on table "child_table"

  • MySQL:Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails…

  • SQL Server:The DELETE statement conflicted with the REFERENCE constraint "FK_Name". The conflict occurred in database "DBName", table "dbo.ChildTable", column 'ParentID'。

在所有数据库中,错误消息都会指明以下内容:

  • 违反了哪个外键约束
  • 哪个表作为子表
  • 仍被引用的是哪个键值

安全地解决被阻止的删除

如果您收到删除被阻止的错误,但确实想删除父行,建议使用以下任一方式:

先删除子记录:如果依赖数据不再需要,先对子行执行 DELETE,然后再删除父记录。

-- Step 1: Remove dependencies
DELETE FROM Orders WHERE CustomerID = 501; 
-- Step 2: Now the parent delete will work
DELETE FROM Customers WHERE CustomerID = 501;

更新子记录引用:如果您想保留子行但删除父行,请将子行更新为引用另一个父 ID。

-- Reassign orders to a "General" customer account (ID 999)
UPDATE Orders SET CustomerID = 999 WHERE CustomerID = 501;

更改约束:在少见的管理场景中,您可以暂时禁用外键检查。此操作风险很高,如不极其谨慎,可能导致数据损坏。

特定数据库的注意事项与行为

虽然 SQL 中 ON DELETE RESTRICT 约束的概念在各个 SQL 数据库中是通用的,但不同引擎在时机和内部逻辑上略有差异。

PostgreSQL、MySQL 与 SQL Server 的行为

在PostgreSQL、MySQL 和 SQL Server 中,ON DELETE RESTRICT 约束会在存在相关子行时阻止删除父行。不过,这些引擎在语法上有以下细微差异:

MySQL:严格遵循 SQL 的 RESTRICT 约束。如果您未指定规则,默认即为 RESTRICT。

-- MySQL: RESTRICT is the default if no rule is specified
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

PostgreSQL:同时支持 RESTRICT 和 NO ACTION 约束。尽管它们的行为几乎相同,RESTRICT 是严格的即时检查,而 NO ACTION 允许延迟检查。

-- PostgreSQL: NO ACTION (can be deferred)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE NO ACTION
);

SQL Server:使用 NO ACTION 作为与标准 RESTRICT 行为等效的约束。其 T-SQL 语法中并不显式使用 RESTRICT 关键字,但会通过阻止删除来达到同样的效果。

-- SQL Server: Uses NO ACTION instead of RESTRICT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE NO ACTION
);

我建议您学习我们的 Introduction to SQL Server 和 Creating PostgreSQL Databases 课程,以了解在创建数据库时不同 SQL 方言之间的差异。

延迟约束与事务行为

在高级数据库配置(如 PostgreSQL 或 Oracle)中,您可能会遇到延迟约束。这意味着约束会在事务提交时检查,而不是在执行 DELETE 语句时立即检查。

在此类系统中,一个通常会被 RESTRICT 阻止的删除,可能会在事务内部暂时看起来成功,但在尝试提交时失败。这允许进行复杂的多步操作,例如在同一事务中删除并重新插入相关数据,同时仍在最终强制完整性。

我们的Introduction to Oracle SQL课程将帮助您进一步理解 Oracle 如何处理查询。

选择合适的 ON DELETE 策略

选择正确的约束有助于在数据完整性与应用可用性之间取得平衡。请考虑以下情形,帮助您在使用 ON DELETE 子句时选择合适的方法。

何时 RESTRICT 最安全

在以下情况下使用 SQL 的 ON DELETE RESTRICT 约束:

  • 数据价值很高:父记录在数据库中很重要且被广泛引用。

  • 需要人工审核:您希望由人工或特定的业务逻辑流程来决定依赖数据的处置。

  • 防止“静默”删除:您希望确保开发者不会因一次 CASCADE 删除而意外清空成千上万行数据。

如果您经常在工作中设计或维护数据库,建议您尝试 SQL Server for Database Administrators 技能路径。

何时考虑替代方案

如果 RESTRICT 对您的系统而言过于严格,请考虑以下选项:

  • 当子记录离开父记录就没有意义时,使用 CASCADE 约束。

  • 当关系是可选的时,使用 SET NULL 约束。

  • 当您有一个“占位符”应接管所有孤立记录以保证系统平稳运行时,使用 SET DEFAULT 约束。

结论

SQL 的 ON DELETE RESTRICT 约束是一项保护性的外键规则,它通过在仍存在依赖的子记录时阻止删除父行,将数据完整性置于便利性之上。通过鼓励有意识的清理策略和明确的删除操作归属,SQL 的 RESTRICT 约束在关系型设计中充当安全防护。作为最佳实践,请始终基于业务逻辑和数据语义来选择 SQL 的删除规则,而不是出于便利或短期需求。

我建议您学习我们的 Database Design 课程,在其中您将学习如何创建和管理数据库,并选择适合您需求的 DBMS。我还建议尝试我们的 Associate Data Engineer in SQL 职业路径,学习数据工程与数据仓库的基础知识。

Allan Ouko

作者

Allan Ouko

数据科学技术写作者,具备数据分析、商业智能和数据科学的一线实践经验。我撰写以行业为导向的实用内容,涵盖 SQL、Python、Power BI、Databricks 和数据工程,并以真实的分析工作为基础。我的写作兼顾技术深度与业务影响,帮助专业人士将数据转化为自信的决策。

SQL ON DELETE RESTRICT 常见问答

RESTRICT 与 NO ACTION 有何区别?

在大多数数据库中,它们的行为相同。RESTRICT 立即检查,而 NO ACTION 可能在语句或事务结束时检查。

当我尝试删除受限制的父行时会发生什么?

DELETE 操作会失败,数据库将抛出外键约束违规错误。

ON DELETE RESTRICT 与 ON DELETE CASCADE 有何不同?

RESTRICT 会完全阻止删除,而 CASCADE 会自动删除所有依赖的子行。

如何解决被 ON DELETE RESTRICT 阻止的 DELETE?

如何解决被 ON DELETE RESTRICT 阻止的删除?

我可以将现有外键改为使用 ON DELETE RESTRICT 吗?

可以。您可以使用 ALTER TABLE 删除并重新创建带有 ON DELETE RESTRICT 的外键约束。

主题