MySQL文件夹表约束设计：确保同父目录下文件夹名称唯一

问题说明 #

要实现一个存储上传附件及所属文件夹的系统，folders表需满足以下需求：

• 文件夹可以是根目录（parentid为NULL），也可以关联其他文件夹作为父目录
• 同一父目录下（包括根目录）不能存在同名文件夹
• 子文件夹的创建者（uploaderid）必须与父文件夹的创建者一致

原表结构存在两个核心问题：

1. 因parentid允许为NULL，无法直接用parentid + foldername的唯一键约束同名根文件夹（MySQL将NULL视为不同值）
2. 缺少子文件夹与父文件夹创建者一致的约束

原表结构：

CREATE TABLE IF NOT EXISTS `folders` (
    `uploaderid` int(11) NOT NULL,
    `parentid` int(11) unsigned NULL,
    `folderid` int(11) unsigned AUTO_INCREMENT,
    `foldername` VARCHAR(255) NOT NULL,
    -- @TODO 确保子文件夹的uploaderid与父文件夹的uploaderid一致
    UNIQUE KEY unique_folderid (folderid),
    FOREIGN KEY (parentid) REFERENCES folders(folderid) ON DELETE CASCADE,
    FOREIGN KEY (uploaderID) REFERENCES accounts(id) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

优化方案 #

方案1：用特殊值替代NULL表示根目录 #

将parentid的默认值设为0（MySQL自增默认从1开始，不会与folderid冲突），这样就能创建包含parentid的唯一键：

CREATE TABLE IF NOT EXISTS `folders` (
    `uploaderid` int(11) NOT NULL,
    `parentid` int(11) unsigned NOT NULL DEFAULT 0, -- 用0标识根文件夹
    `folderid` int(11) unsigned AUTO_INCREMENT,
    `foldername` VARCHAR(255) NOT NULL,
    UNIQUE KEY unique_folderid (folderid),
    -- 唯一约束：同一用户、同一父目录下不能有同名文件夹
    UNIQUE KEY unique_user_parent_folder (`uploaderid`, `parentid`, `foldername`),
    FOREIGN KEY (parentid) REFERENCES folders(folderid) ON DELETE CASCADE,
    FOREIGN KEY (uploaderID) REFERENCES accounts(id) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 加入uploaderid是为了允许不同用户在各自根目录下使用同名文件夹，避免跨用户的约束冲突
• 根文件夹的parentid统一设为0，彻底解决NULL无法参与唯一键的问题

方案2：使用函数索引处理NULL（MySQL 8.0+适用） #

如果希望保留parentid为NULL表示根目录的语义，可以利用MySQL 8.0及以上支持的函数索引，将NULL转换为固定值后加入唯一约束：

CREATE TABLE IF NOT EXISTS `folders` (
    `uploaderid` int(11) NOT NULL,
    `parentid` int(11) unsigned NULL,
    `folderid` int(11) unsigned AUTO_INCREMENT,
    `foldername` VARCHAR(255) NOT NULL,
    UNIQUE KEY unique_folderid (folderid),
    -- 通过IFNULL将NULL转为0，实现根目录下的同名约束
    UNIQUE KEY unique_user_parent_folder (uploaderid, IFNULL(parentid, 0), foldername),
    FOREIGN KEY (parentid) REFERENCES folders(folderid) ON DELETE CASCADE,
    FOREIGN KEY (uploaderID) REFERENCES accounts(id) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 这种方式无需修改原有NULL的语义，同时让所有根目录的parentid在约束中被视为同一个值

补充：子文件夹创建者一致性约束 #

通过触发器实现子文件夹uploaderid与父文件夹一致的验证：

DELIMITER //
CREATE TRIGGER validate_folder_uploader BEFORE INSERT ON folders
FOR EACH ROW
BEGIN
    DECLARE parent_uploader INT;
    -- 仅当存在父文件夹时验证
    IF NEW.parentid IS NOT NULL THEN
        SELECT uploaderid INTO parent_uploader FROM folders WHERE folderid = NEW.parentid;
        IF NEW.uploaderid != parent_uploader THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '子文件夹的上传者必须与父文件夹保持一致';
        END IF;
    END IF;
END //

-- 可选：添加更新时的验证触发器
CREATE TRIGGER validate_folder_uploader_update BEFORE UPDATE ON folders
FOR EACH ROW
BEGIN
    DECLARE parent_uploader INT;
    IF NEW.parentid IS NOT NULL THEN
        SELECT uploaderid INTO parent_uploader FROM folders WHERE folderid = NEW.parentid;
        IF NEW.uploaderid != parent_uploader THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '子文件夹的上传者必须与父文件夹保持一致';
        END IF;
    END IF;
END //
DELIMITER ;

• 插入或更新文件夹时，若存在父文件夹，则检查创建者是否一致，不一致则抛出错误

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