MariaDB按外键实现条件唯一约束：每个Stuff仅一个topMost层级

在MariaDB 10.6中，可以通过生成列（虚拟列）+唯一约束的组合来实现这个需求，完全不需要触发器，具体步骤如下：

1. 定义Heighness表时添加生成列和唯一约束 #

创建Heighness表时，新增一个虚拟生成列，当level为topMost时，该列取值为Stuff_id；否则取值为NULL。然后给这个生成列添加唯一约束。

示例SQL：

CREATE TABLE Heighness (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Stuff_id INT NOT NULL,
    level VARCHAR(20) NOT NULL CHECK (level IN ('topMost', 'mediocre', 'low')), -- 可选，限制level的合法值
    topmost_stuff_id INT AS (CASE WHEN level = 'topMost' THEN Stuff_id ELSE NULL END) STORED, -- 生成列
    UNIQUE KEY uk_topmost_stuff (topmost_stuff_id), -- 唯一约束
    FOREIGN KEY (Stuff_id) REFERENCES Stuff(ID)
);

2. 原理说明 #

• 当level不是topMost时，topmost_stuff_id为NULL。MariaDB的唯一约束不会将多个NULL视为重复值，所以非topMost的记录可以随意重复，完全符合需求。
• 当level是topMost时，topmost_stuff_id等于Stuff_id，此时唯一约束会强制同一个Stuff_id只能对应一条这样的记录，重复插入就会触发唯一键冲突错误。

3. 测试验证 #

• 插入同一Stuff_id的多条非topMost记录：可以正常插入，不会报错。
• 插入同一Stuff_id的第一条topMost记录：正常插入。
• 插入同一Stuff_id的第二条topMost记录：触发Duplicate entry 'X' for key 'uk_topmost_stuff'错误，符合预期。

注意事项 #

• 生成列使用STORED（存储型）还是VIRTUAL（虚拟型）都可以，STORED会占用存储空间但查询更快，VIRTUAL不占空间但每次查询计算，根据实际场景选择即可。
• 如果需要对level的取值范围做限制，可以加上CHECK约束，避免非法值插入。

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