MySQL中如何批量检测待删除员工记录是否被外键关联？

我有一个存储员工数据的employee表，表结构如下：

当尝试删除员工时，若其他表存在关联该员工ID的外键条目，会触发外键异常，这能保障数据库数据完整性，避免出现孤立条目。但关联employee表的外键表数量众多，逐个检查待删除员工（如emp_id=1）是否在这些表中有条目过于繁琐。

我已通过以下SQL查询到所有关联employee表emp_id的外键表信息：

SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    CONSTRAINT_NAME
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    REFERENCED_TABLE_NAME = 'employee';

现在想知道能否通过一个SQL查询，批量检测待删除的员工记录是否在这些关联表中有对应条目，我尝试了如下SQL，请问该方案是否可行？

SELECT
    emps.* 
FROM
    employees emps
WHERE EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    WHERE kcu.REFERENCED_TABLE_NAME = 'employee'
    AND (
        (kcu.TABLE_NAME = emps.TABLE_NAME AND kcu.COLUMN_NAME =emps.emp_id
    )
);

你给出的SQL方案完全不可行，核心问题有两个：

1. emps是employee表的别名，而employee表根本没有TABLE_NAME字段，emps.TABLE_NAME属于无效引用，条件永远不成立。
2. 子查询仅在查询数据库的元数据（外键定义），并没有实际去关联的业务表中检查是否存在对应emp_id的记录，逻辑完全偏离需求。

要实现批量检测待删除员工是否存在外键关联记录，可参考以下两种思路：

思路1：动态生成检测SQL #

利用你已有的元数据查询结果，自动拼接出检测关联记录的SQL。比如先执行元数据查询得到所有TABLE_NAME和COLUMN_NAME，再拼接成如下格式的查询：

SELECT 
    emp_id,
    EXISTS(SELECT 1 FROM 表名1 WHERE 外键字段1 = e.emp_id) AS 表名1关联状态,
    EXISTS(SELECT 1 FROM 表名2 WHERE 外键字段2 = e.emp_id) AS 表名2关联状态
    -- 依次添加所有关联表的检测逻辑
FROM employee e
WHERE emp_id IN (1,2); -- 指定待检测的员工ID

不同数据库的动态SQL实现方式不同：MySQL可通过存储过程拼接执行，PostgreSQL可使用EXECUTE语句实现。

思路2：用UNION ALL合并关联表查询 #

如果关联表数量不多，可手动编写包含所有关联表的查询，直接找出所有存在外键关联的员工ID：

SELECT emp_id FROM employee 
WHERE emp_id IN (
    SELECT 外键字段1 FROM 表名1
    UNION ALL
    SELECT 外键字段2 FROM 表名2
    UNION ALL
    SELECT 外键字段3 FROM 表名3
    -- 继续添加所有关联表的查询
);

该查询会返回所有在至少一个关联表中有记录的员工ID，也就是无法直接删除的员工。

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