Mysql唯一约束允许组合中的单行

Question

是否可以设置唯一约束，使得某个特定列的值仅出现一次？

例如：

-----------------------    
name | price | default
-----------------------
XYZ  |  20   | TRUE
-----------------------
XYZ  |  30   | FALSE
-----------------------
XYZ  |  40   | FALSE
-----------------------
ABC  | 50    | FALSE
-----------------------

在上述表格中，对于特定的name值，default值只能出现一次。并且将在name和price列上具有唯一约束。

这种情况是否可行？

- sidgate

---

有趣的问题。据我所知 - 你不能。但让我们等一下，也许我错了。 - Alon Eitan

@sidgate 你应该仔细审查下面给出的周到答案。 - Tim Biegeleisen

Answer 1

一种正常的做法是提取一个单独的表来保存默认价格：

CREATE TABLE price (
    name VARCHAR(255),
    price INT,
    PRIMARY KEY (name, price)
) ;

CREATE TABLE defaultPrice (
    name VARCHAR(255),
    price INT,
    PRIMARY KEY (name),
    FOREIGN KEY(name, price) REFERENCES price(name, price)
);

大多数人会建议引入代理键：

CREATE TABLE item (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    UNIQUE(name)
);

CREATE TABLE price (
    itemId INT,
    price INT,
    PRIMARY KEY (itemId, price),
    FOREIGN KEY (itemId) REFERENCES item (id)
) ;

CREATE TABLE defaultPrice (
    itemId INT,
    price INT,
    PRIMARY KEY (itemId),
    FOREIGN KEY (itemId, price) REFERENCES price (itemId, price)
);

- RandomSeed

---

虽然其他答案提供了解决问题的有效方法，但这个似乎是正确的答案。 - sidgate

Answer 2

我用一种奇怪的方法解决了这个问题，使用TIMESTAMP作为标记。

它被用作“已删除”标记。

如果是NULL，则该记录未被删除 - 相反，如果已删除（<> NULL），则唯一键将永远不会冲突，避免非删除记录的重复注册。

例如，在您的情况下：

您永远不会有2行具有默认值：NULL，但您可以有N行带有排除时间的TIMESTAMP。

- Ragen Dazs

Answer 3

你可以创建一个触发器来检查是否已经存在值为“TRUE”的字段，如果是，则采取相应措施。

请注意，你不能轻易地“拒绝”更新。（例如，请参阅：如何在MySQL触发器中中止INSERT操作？）。

例如，你可以只插入false并通过设置标志来保存错误。

- Nanne

Answer 4

在所有三个列上放置简单的唯一约束条件是行不通的，因为它将允许以下情况：

name | price | default
-----------------------
XYZ  |  20   | TRUE
XYZ  |  30   | TRUE
XYZ  |  40   | FALSE
ABC  |  50   | FALSE

在其他的SQL引擎中，你可以创建一个检查约束来确保对于每组“name”行，具有“default = TRUE”的这些行的数量小于等于1。然而，MySQL不支持强制执行检查约束。

在MySQL中，你可以通过“insert”和“update”触发器来实现这一点：

CREATE TRIGGER `before_insert` BEFORE INSERT ON `tableName`
FOR EACH ROW
BEGIN

   DECLARE @sum INT

   SELECT @sum = SUM(CASE WHEN [default] = TRUE THEN 1 ELSE 0 END)
   FROM tableName
   WHERE [name] = new.[name]

   IF (@sum = 0 OR new.default = FALSE)
   BEGIN
      INSERT INTO tableName (name, price, default)
      VALUES (new.[name], new.[price], new.[defaul]t)

   END
END

同样地，对于 update 也是如此。

- Daniel A. Thompson

Answer 5

这里有一个解决方法，可能适合你的需求。请考虑以下表定义和唯一约束条件：

CREATE TABLE prices
(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    price int,
    default varchar(255)
)

ALTER TABLE prices ADD UNIQUE idx (id, name, default);

在您的应用层中，当使用default为FALSE时插入记录时，请始终将id列保留为NULL。这将导致MySQL始终为id添加唯一值，从而可以输入name和default的重复值（即给定的name可能会多次出现default值为FALSE）。

然而，当您插入一个default为TRUE的记录时，您应始终使用相同的id值。这将确保给定的name只能以TRUE形式出现一次。

- Tim Biegeleisen