如何约束同一数据表中PARENT_ID与CHILD_ID的值互不包含？

我们需要为数据表的父子关联设置强制约束，具体规则如下：

• 禁止将任何已存在于CHILD_ID列的值作为新记录的PARENT_ID
• 禁止将任何已存在于PARENT_ID列的值作为新记录的CHILD_ID

示例验证表 #

解决方案：基于主流数据库的实现方式 #

1. MySQL 实现

MySQL原生CHECK约束不支持跨行校验，需通过前置触发器拦截非法插入/更新：

插入校验触发器

DELIMITER //
CREATE TRIGGER validate_parent_child_before_insert
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    -- 校验新PARENT_ID未出现在CHILD_ID列
    IF EXISTS (SELECT 1 FROM your_table_name WHERE CHILD_ID = NEW.PARENT_ID) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'PARENT_ID不能是已存在的CHILD_ID值';
    END IF;
    -- 校验新CHILD_ID未出现在PARENT_ID列
    IF EXISTS (SELECT 1 FROM your_table_name WHERE PARENT_ID = NEW.CHILD_ID) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CHILD_ID不能是已存在的PARENT_ID值';
    END IF;
END //
DELIMITER ;

更新校验触发器

若需阻止更新操作违反约束，补充以下触发器：

DELIMITER //
CREATE TRIGGER validate_parent_child_before_update
BEFORE UPDATE ON your_table_name
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM your_table_name WHERE CHILD_ID = NEW.PARENT_ID) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'PARENT_ID不能是已存在的CHILD_ID值';
    END IF;
    IF EXISTS (SELECT 1 FROM your_table_name WHERE PARENT_ID = NEW.CHILD_ID) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CHILD_ID不能是已存在的PARENT_ID值';
    END IF;
END //
DELIMITER ;

2. PostgreSQL 实现

PostgreSQL支持自定义函数+CHECK约束的简洁方案：

第一步：创建校验函数

CREATE OR REPLACE FUNCTION validate_parent_child_constraint(p_parent_id VARCHAR, p_child_id VARCHAR)
RETURNS BOOLEAN AS $$
BEGIN
    -- 检查PARENT_ID未出现在CHILD_ID列
    IF EXISTS (SELECT 1 FROM your_table_name WHERE CHILD_ID = p_parent_id) THEN
        RETURN FALSE;
    END IF;
    -- 检查CHILD_ID未出现在PARENT_ID列
    IF EXISTS (SELECT 1 FROM your_table_name WHERE PARENT_ID = p_child_id) THEN
        RETURN FALSE;
    END IF;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql STABLE;

第二步：绑定表约束

ALTER TABLE your_table_name
ADD CONSTRAINT parent_child_validity_check
CHECK (validate_parent_child_constraint(PARENT_ID, CHILD_ID));

注：函数需标记为STABLE，确保约束在表数据变更时能自动重新校验。

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