如果列设置为 x，则 SQL 唯一约束

Question

如果特定列具有特定值，SQL 有没有办法创建列必须唯一的约束？

示例：这些行并未真正删除，而是在数据库中标记为“已删除”。在“未删除”行中，ValueA 必须是唯一的：

ID    ValueA          ValueB            Deleted
-----------------------------------------------------
1     'foo'           10               0
2     'bar'           20               0
3     'bar'           30               1
4     'bar'           40               1
5     'foo'           50               0 --NOT ALLOWED

我想到了类似 CHECK 约束的东西，但我不知道如何做到这一点。

Answer 1

对于 SQL92 这是不可能的，也许你可以用触发器实现一些东西

Answer 2

你能稍微改变一下设计吗？

在我看来，你有一份“东西”清单。对于每个 ValueA，在任何时候都有一个活动的“事物”。最好按如下方式建模：

1. 从您的主 Thingies 表中删除 ValueA 和 Deleted。

2. 创建一个包含 ValueA 和 ID 列的新表 ActiveThingies。通过将 ValueA 设置为唯一键或主键来保护此表。 （您可能还需要使 ID 唯一，具体取决于单个 ID 是否可以表示超过 1 个 ValueA）。

现在，使用 ActiveThingies 表随时控制哪条记录是当前的。要更改“foo”的活动（未删除）记录，请更新 ActiveThingies 中的 ID 列。

要获取未删除项目的列表，请连接两个表。

然而，通过这种设计，您将无法记住“已删除”“事物”的 ValueA。如果您需要记住这些值，您还需要在 Thingies 中包含 ValueA 列。

Answer 3

此问题有解决方法 - 创建另一个列 deleted_on

deleted_on 时间戳 NOT NULL DEFAULT '0000-00-00 00:00:00'

并在 ValueA 和 deleted_on 上创建唯一键

UNIQUE KEY not_deleted（ValueA，deleted_on）

软删除记录时插入 NOW() 作为 deleted_on 的值

Answer 4

MySQL 忽略 CHECK 约束，因此您无法像在其他数据库中那样在 MySQL 中执行此操作。

这是一个黑客。 valueA + 已删除的唯一约束。当删除行时，你不能只使用 1，它们必须是 1、2、3...

这至少可以让你在 MySQL 的服务器端执行此操作，但引入了一个步骤。当将一行标记为删除时，您必须首先找到最大值（已删除），加1，然后在标记为删除时插入该值。

Answer 5

将表拆分为两个表：一张对 ValueA 有 UNIQUE 约束，另一张则没有。使用视图+触发器组合两个表。像这样的东西：（

CREATE TABLE _Active (
    ID       INTEGER,
    ValueA   VARCHAR(255) UNIQUE,
    ValueB   INTEGER
);

CREATE TABLE _Deleted (
    ID       INTEGER,
    ValueA   VARCHAR(255), /* NOT unique! */
    ValueB   INTEGER
);

CREATE VIEW Thingies AS
    SELECT ID, ValueA, ValueB, 0 AS Deleted FROM _Active
UNION ALL
    SELECT ID, ValueA, ValueB, 1 AS Deleted FROM _Deleted;

CREATE TRIGGER _trg_ii_Thingies_Active
    INSTEAD OF INSERT ON Thingies
    FOR EACH ROW WHEN NOT NEW.Deleted
BEGIN
    INSERT INTO _Active(ID, ValueA, ValueB)
        VALUES (NEW.ID, NEW.ValueA, NEW.ValueB);
END;

CREATE TRIGGER _trg_ii_Thingies_Deleted
    INSTEAD OF INSERT ON Thingies
    FOR EACH ROW WHEN NEW.Deleted
BEGIN
    INSERT INTO _Deleted(ID, ValueA, ValueB)
        VALUES (NEW.ID, NEW.ValueA, NEW.ValueB);
END;

/* Add triggers for DELETE and UPDATE as appropriate */

我不确定 CREATE TRIGGER 语法，但你知道我的意思。）