如何在执行级联删除前自动获取所有将被删除的行？

要自动发现并获取级联删除时所有受影响的行，核心思路是利用数据库系统的元数据（系统表）自动识别外键关联关系，再动态生成查询抓取所有依赖数据。这种方案无需手动维护关联表列表，能自动适配未来新增的外键，完全用SQL实现。

以下分主流数据库给出具体实现思路：

1. MySQL 实现 #

MySQL通过information_schema库中的系统表存储外键信息，可通过以下步骤实现：

步骤1：查询所有关联目标主键的外键关系 #

先找出所有引用目标表主键的外键表、外键列和关联列：

SELECT
  rc.constraint_name,
  kcu.table_name AS foreign_table,
  kcu.column_name AS foreign_column,
  kcu.referenced_table_name AS primary_table,
  kcu.referenced_column_name AS primary_column
FROM
  information_schema.REFERENTIAL_CONSTRAINTS rc
JOIN
  information_schema.KEY_COLUMN_USAGE kcu
ON
  rc.constraint_name = kcu.constraint_name
WHERE
  kcu.referenced_table_name = '你的目标表名'
  AND kcu.referenced_column_name = '你的主键列名'
  AND rc.delete_rule = 'CASCADE'; -- 只筛选级联删除的外键

步骤2：动态生成查询获取受影响行 #

编写存储过程，输入目标表名和主键值，自动遍历所有关联表并查询数据：

DELIMITER //
CREATE PROCEDURE GetCascadeDeleteRows(
  IN p_primary_table VARCHAR(64),
  IN p_primary_column VARCHAR(64),
  IN p_primary_value VARCHAR(255)
)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_foreign_table VARCHAR(64);
  DECLARE v_foreign_column VARCHAR(64);
  DECLARE cur CURSOR FOR
    SELECT kcu.table_name, kcu.column_name
    FROM information_schema.REFERENTIAL_CONSTRAINTS rc
    JOIN information_schema.KEY_COLUMN_USAGE kcu
      ON rc.constraint_name = kcu.constraint_name
    WHERE kcu.referenced_table_name = p_primary_table
      AND kcu.referenced_column_name = p_primary_column
      AND rc.delete_rule = 'CASCADE';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  -- 先查询目标表本身的行
  SET @sql = CONCAT('SELECT "', p_primary_table, '" AS table_name, * FROM ', p_primary_table, ' WHERE ', p_primary_column, ' = ', QUOTE(p_primary_value));
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  -- 遍历所有关联表查询
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_foreign_table, v_foreign_column;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('SELECT "', v_foreign_table, '" AS table_name, * FROM ', v_foreign_table, ' WHERE ', v_foreign_column, ' = ', QUOTE(p_primary_value));
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

调用方式：CALL GetCascadeDeleteRows('users', 'user_id', '123');，会返回目标表及所有关联表中即将被删除的行，并标记所属表名。

2. PostgreSQL 实现 #

PostgreSQL通过pg_constraint、pg_class等系统表存储元数据，支持递归处理多层级联：

步骤1：查询外键关联关系 #

SELECT
  conname AS constraint_name,
  pg_class.relname AS foreign_table,
  pg_attribute.attname AS foreign_column,
  pg_class_ref.relname AS primary_table,
  pg_attribute_ref.attname AS primary_column
FROM
  pg_constraint
JOIN
  pg_class ON pg_constraint.conrelid = pg_class.oid
JOIN
  pg_attribute ON pg_constraint.conrelid = pg_attribute.attrelid
  AND pg_attribute.attnum = ANY(pg_constraint.conkey)
JOIN
  pg_class pg_class_ref ON pg_constraint.confrelid = pg_class_ref.oid
JOIN
  pg_attribute pg_attribute_ref ON pg_constraint.confrelid = pg_attribute_ref.attrelid
  AND pg_attribute_ref.attnum = ANY(pg_constraint.confkey)
WHERE
  pg_constraint.contype = 'f'
  AND pg_class_ref.relname = '你的目标表名'
  AND pg_attribute_ref.attname = '你的主键列名'
  AND pg_constraint.confdeltype = 'c'; -- 筛选级联删除的外键（'c'代表CASCADE）

步骤2：递归查询所有层级受影响行 #

用PL/pgSQL编写函数，递归处理多层级联（如A→B→C的嵌套关联）：

CREATE OR REPLACE FUNCTION GetCascadeDeleteRows(
  p_primary_table text,
  p_primary_column text,
  p_primary_value text
) RETURNS TABLE(table_name text, row_data json) AS $$
DECLARE
  v_rec record;
  v_sql text;
BEGIN
  -- 查询目标表行
  v_sql := format('SELECT %L AS table_name, row_to_json(t) AS row_data FROM %I t WHERE %I = %L',
                  p_primary_table, p_primary_table, p_primary_column, p_primary_value);
  RETURN QUERY EXECUTE v_sql;

  -- 遍历所有直接关联的外键表，递归查询其关联行
  FOR v_rec IN
    SELECT pg_class.relname AS foreign_table, pg_attribute.attname AS foreign_column
    FROM pg_constraint
    JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
    JOIN pg_attribute ON pg_constraint.conrelid = pg_attribute.attrelid
      AND pg_attribute.attnum = ANY(pg_constraint.conkey)
    JOIN pg_class pg_class_ref ON pg_constraint.confrelid = pg_class_ref.oid
    JOIN pg_attribute pg_attribute_ref ON pg_constraint.confrelid = pg_attribute_ref.attrelid
      AND pg_attribute_ref.attnum = ANY(pg_constraint.confkey)
    WHERE pg_constraint.contype = 'f'
      AND pg_class_ref.relname = p_primary_table
      AND pg_attribute_ref.attname = p_primary_column
      AND pg_constraint.confdeltype = 'c'
  LOOP
    RETURN QUERY SELECT * FROM GetCascadeDeleteRows(
      v_rec.foreign_table, v_rec.foreign_column, p_primary_value
    );
  END LOOP;
END;
$$ LANGUAGE plpgsql;

调用方式：SELECT * FROM GetCascadeDeleteRows('users', 'user_id', '123');，返回所有层级的受影响行，以JSON格式存储行数据。

3. SQL Server 实现 #

SQL Server通过sys.foreign_keys、sys.foreign_key_columns等系统视图查询外键：

步骤1：查询外键关联 #

SELECT
  fk.name AS constraint_name,
  OBJECT_NAME(fk.parent_object_id) AS foreign_table,
  c.name AS foreign_column,
  OBJECT_NAME(fk.referenced_object_id) AS primary_table,
  rc.name AS primary_column
FROM
  sys.foreign_keys fk
JOIN
  sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN
  sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN
  sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
WHERE
  OBJECT_NAME(fk.referenced_object_id) = '你的目标表名'
  AND rc.name = '你的主键列名'
  AND fk.delete_referential_action = 1; -- 1代表CASCADE

步骤2：动态SQL查询 #

可编写存储过程遍历关联表，实现类似MySQL的查询逻辑，返回所有受影响行。

关键注意事项 #

• 权限要求：执行上述SQL需要有查询系统表/视图的权限（如MySQL的SELECT权限on information_schema，PostgreSQL的USAGE权限on pg_catalog）。
• 循环引用处理：若存在表之间的循环外键引用，需在逻辑中加入判断（如记录已查询过的表），避免无限递归。
• 性能考量：超大型数据库中，遍历系统表和动态查询可能有性能开销，可考虑缓存外键关系并定期刷新。

内容的提问来源于stack exchange，提问作者Hari Prasandh