MySQL中如何原生约束One-level one-to-many父子关系？

你的需求是强制表中仅存在一级父子关系：即只有ParentID为NULL的记录才能作为父节点，ParentID非空的记录不能被其他记录引用为父节点。MySQL没有直接的内置约束（如特殊外键规则）实现这一点，但可以通过触发器在数据库层面强制该规则，无需完全依赖应用代码。

1. 基础表结构（带外键约束） #

首先创建带基础外键的表，确保ParentID只能引用存在的ID：

CREATE TABLE app_info (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    ParentID INT NULL,
    FOREIGN KEY (ParentID) REFERENCES app_info(ID)
        ON DELETE CASCADE -- 可选：父节点删除时自动删除子节点，或设为ON DELETE SET NULL
);

2. 触发器实现一级关系约束 #

需要两个触发器分别处理插入和更新操作，拦截违反规则的操作：

插入时检查：禁止创建“子节点的子节点” #

当插入新记录时，如果指定了ParentID，必须确保被引用的父节点本身是顶级节点（ParentID为NULL）：

DELIMITER //
CREATE TRIGGER trg_app_info_insert_check_level
BEFORE INSERT ON app_info
FOR EACH ROW
BEGIN
    IF NEW.ParentID IS NOT NULL THEN
        DECLARE parent_parent_id INT;
        SELECT ParentID INTO parent_parent_id FROM app_info WHERE ID = NEW.ParentID;
        IF parent_parent_id IS NOT NULL THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误：不能创建子节点的子节点，父节点本身存在上级';
        END IF;
    END IF;
END //
DELIMITER ;

更新时检查：禁止修改节点层级 #

更新操作需要处理两种违规情况：

• 不能将记录的父节点改为非顶级节点
• 不能将已有子节点的顶级节点改为子节点

DELIMITER //
CREATE TRIGGER trg_app_info_update_check_level
BEFORE UPDATE ON app_info
FOR EACH ROW
BEGIN
    -- 检查新父节点是否为顶级
    IF NEW.ParentID IS NOT NULL THEN
        DECLARE parent_parent_id INT;
        SELECT ParentID INTO parent_parent_id FROM app_info WHERE ID = NEW.ParentID;
        IF parent_parent_id IS NOT NULL THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误：不能将父节点设置为非顶级节点';
        END IF;
    END IF;

    -- 检查是否将已有子节点的顶级节点改为子节点
    IF OLD.ParentID IS NULL AND NEW.ParentID IS NOT NULL THEN
        DECLARE child_count INT;
        SELECT COUNT(*) INTO child_count FROM app_info WHERE ParentID = OLD.ID;
        IF child_count > 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误：该节点已有子节点，不能转为子节点';
        END IF;
    END IF;
END //
DELIMITER ;

3. 效果验证 #

• 插入顶级节点（ParentID为NULL）：正常执行
• 插入顶级节点的子节点：正常执行
• 插入子节点的子节点：触发器抛出错误，操作被拦截
• 将已有子节点的顶级节点改为子节点：触发器抛出错误，操作被拦截

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