如何用自我引用的外键从表中删除行

要安全删除具有自引用外键的表中的行，必须先处理依赖关系，可采用以下三种方法：1. 若可修改表结构，使用ON DELETE CASCADE，删除父行时自动级联删除所有子行，但需谨慎以防意外删除大量数据；2. 手动控制删除顺序，通过递归CTE先删除叶节点，逐步向上删除，例如使用WITH RECURSIVE子句定位指定节点及其所有下属并批量删除；3. 仅在受控环境下临时禁用外键检查（SET FOREIGN_KEY_CHECKS = 0），操作后重新启用，但此法风险高，可能导致数据不一致。最佳实践是结合递归CTE精确删除子树或在明确业务逻辑下启用级联删除，且始终在操作前备份数据以确保安全。

Deleting rows from a table that has a self-referencing foreign key (i.e., a foreign key that references the same table's primary key) can be tricky because of referential integrity constraints. If a row is referenced by another row in the same table, you'll get a foreign key violation when trying to delete it.

Here’s how to safely delete such rows:

1. Understand the Structure

A self-referencing foreign key typically appears in hierarchical data, like an employee table where each employee has a manager, and the manager_id references the id of another employee:

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employee(id)
);

In this case, you can't just delete any row — if someone is a manager (id used in manager_id), you must handle those dependencies first.

2. Delete in the Correct Order (Leaf Nodes First)

The safest approach is to delete child rows before parent rows — that is, remove the leaf nodes (those not referenced by others) first, and work up to the top-level nodes.

But since you can't always predict the hierarchy, use a recursive or iterative strategy.

Option A: Use ON DELETE CASCADE (if you control the schema)

Modify the foreign key to automatically delete dependent rows:

ALTER TABLE employee
DROP FOREIGN KEY employee_ibfk_1; -- or whatever constraint name

ALTER TABLE employee
ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id) REFERENCES employee(id)
ON DELETE CASCADE;

Now, deleting a row will automatically delete all employees under it (and their subordinates, recursively).

Warning: This can delete large portions of your data unintentionally. Use with caution.

Option B: Delete in Multiple Steps (Manual Control)

If you can't or don't want to use ON DELETE CASCADE, delete rows in batches — starting from the "bottom" of the hierarchy.

Step-by-step:

1. Identify rows that are not referenced as managers (safe to delete first):

   DELETE FROM employee
   WHERE id NOT IN (
       SELECT * FROM (
           SELECT DISTINCT manager_id
           FROM employee
           WHERE manager_id IS NOT NULL
       ) AS tmp
   );

   Note: The double wrapper (tmp) is needed in MySQL to avoid errors about modifying the same table in a subquery.

2. Repeat the deletion until no more rows are affected.

   This gradually removes leaf nodes, freeing up higher-level nodes for deletion.

3. Alternatively, delete specific subtree (e.g., everyone under a certain manager):

   Use a recursive CTE (Common Table Expression) to find all descendants:

   WITH RECURSIVE subordinates AS (
       -- Start with the target employee
       SELECT id
       FROM employee
       WHERE id = 123  -- ID to delete (and all under it)
   
       UNION ALL
   
       -- Recursively add all employees reporting to them
       SELECT e.id
       FROM employee e
       INNER JOIN subordinates s ON e.manager_id = s.id
   )
   DELETE FROM employee
   WHERE id IN (SELECT id FROM subordinates);

   This safely deletes a full hierarchy starting from a given node.

3. Temporarily Disable Foreign Key Checks (Not Recommended for Production)

As a last resort (e.g., during maintenance), you can disable constraint checking:

SET FOREIGN_KEY_CHECKS = 0;

DELETE FROM employee WHERE id = 123;

SET FOREIGN_KEY_CHECKS = 1;

⚠️ Dangerous: This can leave orphaned references or break data integrity. Only use in controlled environments, and only if you're certain about the data state.

Summary

• Best practice: Use recursive CTEs to delete entire subtrees safely.
• For automation: Consider ON DELETE CASCADE if the data model allows it.
• Avoid: Disabling foreign key checks unless absolutely necessary.

Handling self-referencing tables requires care — always back up your data before bulk deletions.

Basically, it's about managing dependencies: delete children before parents, or let the database do it automatically with cascade rules.

以上是如何用自我引用的外键从表中删除行的详细内容。更多信息请关注PHP中文网其他相关文章！