如何在MySQL布尔列上创建“唯一”约束？

Question

我想在MySQL表中添加一个名为is_default的BOOLEAN列。在该列中，只有一条记录可以将is_default设置为true。

如何在MySQL中向我的列添加此约束条件？

谢谢！

---

更新

如果不是应该添加的约束条件，我们如何处理这种类型的问题？

- benjisail

Answer 1

我认为这不是模拟单个默认值情况的最佳方式。

相反，我会去掉 IsDefault 列，并创建一个只包含主表主键字段的单独表格。在这个表格中，您可以放置标识默认记录的 PK 值。

这样可大大减少存储空间，并避免更新时暂时没有默认值（或者临时有两个默认值）的问题。

您有很多选项来确保默认表格中只有一行。

- Larry Lustig

---

确保默认表中只有一行的选项是什么？ - benjisail

我也想知道那个问题的答案。 - ryvantage

有人能详细解释一下这个答案吗？一个只有1行的表如何确保主表中只有1行默认行？这里提出的单行表将如何与主表相关联？它会被连接吗？ - theyuv

Answer 2

在MySQL中，你不能使用这样的约束条件。

但是，如果你使用TRUE和NULL值代替TRUE和FALSE，则可以工作，因为UNIQUE列可以有多个NULL值。请注意，这并不适用于所有数据库，但在MySQL中有效。

CREATE TABLE table1(b BOOLEAN UNIQUE);

INSERT INTO table1 (b) VALUES (TRUE);   // Succeeds
INSERT INTO table1 (b) VALUES (TRUE);   // Fails: duplicate entry '1' for key 'b'

INSERT INTO table1 (b) VALUES (FALSE);  // Succeeds
INSERT INTO table1 (b) VALUES (FALSE);  // Fails: duplicate entry '0' for key 'b'

INSERT INTO table1 (b) VALUES (NULL);   // Succeeds
INSERT INTO table1 (b) VALUES (NULL);   // Succeeds!

- Mark Byers

---

有没有一种解决方案适用于所有主要数据库？ - benjisail

@benjisail：是的，我可以想到至少一种方法来做到这一点，但它是一个丑陋的hack（比我的当前答案更丑陋），所以我不确定是否应该发布它，以防你实际实现它！ - Mark Byers

Answer 3

我们如何在数据库处理这种类型的问题？

在一些数据库管理系统中，您可以创建部分索引。

在PostgreSQL中，操作如下：

CREATE UNIQUE INDEX only_one_true 
  ON the_table (is_default)
  WHERE is_default

SQL Server 2008也有一个非常类似的语法。

在Oracle上，这可能会更加复杂，但同样可行：

CREATE UNIQUE INDEX only_one_true 
  ON the_table (CASE 
                  WHEN is_default = 1 THEN 1
                  ELSE null
                END)

Oracle解决方案在任何支持索引定义表达式的数据库管理系统上都可以使用。

- user330315

Answer 4

请查看触发器。我相信它们是在5.0.2版本中引入的。你需要一个“插入前”触发器。如果已经有一行is_default=true，就会引发错误。我不知道并发等方面可能出现什么问题，但希望这足以让你开始了。

- Ciaran Keating

Answer 5

我认为问题不在于数据库，而是在于您的模型。由于您没有提及要表示的数据类型，因此很难给出解决方案的好例子，但是一个XXXType或XXXConfiguration表可以拥有一个defaultXXXId列。

想象一下：蓝色应该知道它是默认值，还是其他东西应该知道在给定上下文中使用蓝色时它是默认值？

改变数据建模方式通常比尝试使用特定数据库风格的特定功能来表示数据更适合跨数据库兼容性，如果您考虑到问题域，这种方法可能并不自然。

- Klaus Byskov Pedersen

Answer 6

MySQL不支持检查约束，这是使用触发器的解决方案：

如果不存在myTable，则创建表：
create table if not exists myTable (
    id int not null auto_increment primary key,
    is_default bit not null
) engine=innodb;
选择“创建触发器tbi_myTable”；
如果存在tbi_myTable，则删除触发器。
设置分隔符//
创建触发器tbi_myTable，每次插入myTable时都会执行以下操作：
begin
    如果（select count(1) from myTable where is_default=true）> 0 && NEW.is_default then
        -- Signal仅在5.6及以上版本中使用另一种方法来引发错误：如果小于5.6
        SIGNAL SQLSTATE '50000' SET MESSAGE_TEXT = '不允许只有一个is_default为true的行插入到myTable中！';
    end if;
END //
设置分隔符;
向myTable插入数据：
insert into myTable (is_default) values (false);
insert into myTable (is_default) values (true);
insert into myTable (is_default) values (false);
insert into myTable (is_default) values (false);
-- 这将生成一个错误
insert into myTable (is_default) values (true);
insert into myTable (is_default) values (false);
从myTable中选择所有数据：
select * from myTable;
-- 将给出以下结果
/*
id  is_default
1   false
2   true
3   false
4   false
*/

- Benoit