MySQL表能否添加约束保证每个唯一domain至少有一行default值为1

可以实现，MySQL 原生行级 CHECK 约束无法满足跨行校验的需求，需要通过触发器实现全场景的规则校验，具体方案如下：

实现逻辑说明 #

• 原生 CHECK 约束仅能校验当前操作行的字段值，无法统计同 domain 下其他行的 default 字段值，因此无法直接实现需求。
• 触发器可以在数据插入、更新、删除前校验全表匹配数据，同时可以适配单条插入、批量插入的场景。

触发器实现代码 #

注意default是MySQL保留关键字，使用时需要用反引号包裹，替换代码中your_table_name为实际表名即可：

1. 插入前触发器（校验插入规则） #

用于拦截仅插入新domain的default=0记录的请求，同时支持同批次插入同一domain的default=1和default=0记录：

DELIMITER //
CREATE TRIGGER check_domain_default_before_insert
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    IF NEW.`default` = 0 THEN
        DECLARE has_default_one INT;
        -- 先校验已有记录中是否存在该domain的default=1行
        SELECT COUNT(*) INTO has_default_one 
        FROM your_table_name 
        WHERE domain = NEW.domain AND `default` = 1;
        
        IF has_default_one = 0 THEN
            -- 校验本次批量插入中是否有同domain的default=1行
            SELECT COUNT(*) INTO has_default_one
            FROM INFORMATION_SCHEMA.TEMP_TABLES
            WHERE TABLE_NAME = 'tmp_insert_batch_mark';
            IF has_default_one = 0 THEN
                CREATE TEMPORARY TABLE tmp_insert_batch_mark (domain VARCHAR(255) PRIMARY KEY);
            END IF;
            SELECT COUNT(*) INTO has_default_one FROM tmp_insert_batch_mark WHERE domain = NEW.domain;
            
            IF has_default_one = 0 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入失败：该domain下不存在default=1的记录';
            END IF;
        END IF;
    ELSE
        -- 插入default=1的行时，往临时表打标记，供同批次其他行校验
        DECLARE tmp_exists INT;
        SELECT COUNT(*) INTO tmp_exists
        FROM INFORMATION_SCHEMA.TEMP_TABLES
        WHERE TABLE_NAME = 'tmp_insert_batch_mark';
        IF tmp_exists = 0 THEN
            CREATE TEMPORARY TABLE tmp_insert_batch_mark (domain VARCHAR(255) PRIMARY KEY);
        END IF;
        INSERT IGNORE INTO tmp_insert_batch_mark (domain) VALUES (NEW.domain);
    END IF;
END //
DELIMITER ;

2. 插入后触发器（清理临时表） #

DELIMITER //
CREATE TRIGGER check_domain_default_after_insert
AFTER INSERT ON your_table_name
FOR EACH ROW
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tmp_insert_batch_mark;
END //
DELIMITER ;

3. 更新前触发器（避免修改唯一default=1记录） #

防止将某domain下唯一的default=1记录修改为0：

DELIMITER //
CREATE TRIGGER check_domain_default_before_update
BEFORE UPDATE ON your_table_name
FOR EACH ROW
BEGIN
    IF OLD.`default` = 1 AND NEW.`default` = 0 THEN
        DECLARE default_one_cnt INT;
        SELECT COUNT(*) INTO default_one_cnt
        FROM your_table_name
        WHERE domain = OLD.domain AND `default` = 1;
        
        IF default_one_cnt = 1 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '修改失败：该domain下仅存这一条default=1的记录';
        END IF;
    END IF;
END //
DELIMITER ;

4. 删除前触发器（避免删除唯一default=1记录） #

防止将某domain下唯一的default=1记录删除：

DELIMITER //
CREATE TRIGGER check_domain_default_before_delete
BEFORE DELETE ON your_table_name
FOR EACH ROW
BEGIN
    IF OLD.`default` = 1 THEN
        DECLARE default_one_cnt INT;
        SELECT COUNT(*) INTO default_one_cnt
        FROM your_table_name
        WHERE domain = OLD.domain AND `default` = 1;
        
        IF default_one_cnt = 1 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '删除失败：该domain下仅存这一条default=1的记录';
        END IF;
    END IF;
END //
DELIMITER ;

效果验证 #

• 单独插入domain=facebook.com、default=0的记录会触发报错，执行失败。
• 同批次插入domain=facebook.com的default=1和default=0两条记录可以正常执行。
• 无法删除或修改某domain下唯一的default=1记录，避免出现domain下无default=1记录的情况。

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