如何实现只能在单行上设置的“默认”标志

例如，使用类似于以下的表：

create table foo(bar int identity, chk char(1) check (chk in('Y', 'N')));

将该标志实现为char(1)，bit还是任何形式都没有关系。我只想能够强制执行只能在单行上设置的约束。

受限于MySQL的这个问题的启发

问题的措辞暗示使用表必须是错误的答案。但是有时（大多数时候？）添加另一个表是个好主意。并且添加表完全与数据库无关。

#1 楼

SQL Server 2008-筛选出的唯一索引

CREATE UNIQUE INDEX IX_Foo_chk ON dbo.Foo(chk) WHERE chk = 'Y'

#2 楼

SQL Server 2000、2005：

您可以利用以下事实：唯一索引中只能包含一个null：

create table t( id int identity, 
                chk1 char(1) not null default 'N' check(chk1 in('Y', 'N')), 
                chk2 as case chk1 when 'Y' then null else id end );
create unique index u_chk on t(chk2);

2000，则可能需要SET ARITHABORT ON（感谢@gbn提供此信息）

#3 楼

Oracle：

由于Oracle不索引所有索引列均为空的条目，因此可以使用基于函数的唯一索引：

create table foo(bar integer, chk char(1) not null check (chk in('Y', 'N')));
create unique index idx on foo(case when chk='Y' then 'Y' end);

此索引最多只能索引单个行。

知道此索引的事实后，您也可以以稍微不同的方式实现bit列：

create table foo(bar integer, chk char(1) check (chk ='Y') UNIQUE);

此处chk列的可能值为Y和NULL。最多仅一行可以具有值Y.

chk是否需要一个非null约束？

–杰克·道格拉斯（Jack Douglas）
2011年8月19日下午14:50

@jack：如果您不希望使用null，则可以添加一个非null的约束（问题规格对我来说还不清楚）。在任何情况下，只有一行可以具有值“ Y”。

– Vincent Malgrat
2011年8月19日下午14:54

+1我明白您的意思-没错，这没必要（但是也许更整洁，尤其是与默认值结合使用时）？

–杰克·道格拉斯（Jack Douglas）
2011年8月19日14:57

@jack：您的发言使我意识到，如果您接受该列可以为Y或null，则可以使用一种更简单的方法，请参阅我的更新。

– Vincent Malgrat
2011年8月19日15:11

选项2具有额外的好处，因为跳过了空值，索引将很小-也许是以一定的清晰度为代价的

–杰克·道格拉斯（Jack Douglas）
2011年8月19日在16:09

#4 楼

我认为这是正确构造数据库表的一种情况。更具体地说，如果您有一个具有多个地址的人员，并且希望将其设为默认地址，我认为您应该将默认地址的addressID存储在人员表中，而不要在地址表中包含默认列：

Person
-------
PersonID
Name
etc.
DefaultAddressID (fk to addressID)

Address
--------
AddressID
Street
City, State, Zip, etc.

您可以将DefaultAddressID设置为可空，但是这种结构会强制您执行约束。

#5 楼

MySQL：

create table foo(bar serial, chk boolean unique);
insert into foo(chk) values(null);
insert into foo(chk) values(null);
insert into foo(chk) values(false);
insert into foo(chk) values(true);

select * from foo;
+-----+------+
| bar | chk  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
|   3 |    0 |
|   4 |    1 |
+-----+------+

insert into foo(chk) values(true);
ERROR 1062 (23000): Duplicate entry '1' for key 2
insert into foo(chk) values(false);
ERROR 1062 (23000): Duplicate entry '0' for key 2

检查约束在MySQL中被忽略，因此我们必须将null或false视为假，将true视为真。最多1行可以具有chk=true

您可以考虑在插入/更新时添加将false更改为true的触发器作为缺少检查约束的变通办法是一种改进-IMO它不是我希望可以使用char（0），因为它

还很不错，因为当您需要一个可以使用的列时，它也很不错。仅两个值：定义为CHAR（0）NULL的列仅占用一位，并且只能采用值NULL和”

不幸的是，至少对于MyISAM和InnoDB，我得到

ERROR 1167 (42000): The used storage engine can't index column 'chk'

--edit

这毕竟不是一个好的解决方案，因为在MySQL上，boolean是tinyint(1)的同义词，并且因此允许非零值大于0或1。可能bit是更好的选择

这可以回答我对RolandoMySQLDBA的回答的评论：我们可以使用DRI的MySQL解决方案吗？

– gbn
2011年8月19日14:44

尽管由于null，false，true而有些丑陋-我确实想知道是否有更整洁的东西...

–杰克·道格拉斯（Jack Douglas）
2011年8月19日下午14:47

@Jack-+1可以很好地尝试MySQL中的纯DRI方法。

– RolandoMySQLDBA
2011年8月19日在16:07

我建议在这里避免使用false，因为唯一约束只会允许提供这样一个false值。如果null表示false，则应在整个过程中始终使用它-如果可以进行其他验证（例如JSR-303 / hibernate-validator），则可以强制避免false。

–史蒂夫·钱伯斯（Steve Chambers）
2015年1月14日10:00

MySQL / MariaDB的最新版本实现了虚拟列，我相信可以在dba.stackexchange.com/a/144847/94908上找到略微更优雅的解决方案

–马蒂亚斯·温克尔曼（Matthias Winkelmann）
16年7月25日在16:26

#6 楼

SQL Server：

操作方法：

最好的方法是过滤索引。使用具有唯一性的DRI
SQL Server 2008+
计算列。使用DRI
请参阅Jack Douglas的答案。 SQL Server 2005及更低版本
索引/物化视图，类似于筛选索引。使用DRI
所有版本。
触发。使用代码，而不是DRI。
所有版本

如何不这样做：

使用UDF检查约束。这对于并发和快照隔离是不安全的。看到一二三四

#7 楼

PostgreSQL：

create table foo(bar serial, chk char(1) unique check(chk='Y'));
insert into foo default values;
insert into foo default values;
insert into foo(chk) values('Y');

select * from foo;
 bar | chk
-----+-----
   1 |
   2 |
   3 | Y

insert into foo(chk) values('Y');
ERROR:  duplicate key value violates unique constraint "foo_chk_key"

--edit

或（更好），使用唯一的局部索引：

create table foo(bar serial, chk boolean not null default false);
create unique index foo_i on foo(chk) where chk;
insert into foo default values;
insert into foo default values;
insert into foo(chk) values(true);

select * from foo;
 bar | chk
-----+-----
   1 | f
   2 | f
   3 | t
(3 rows)

insert into foo(chk) values(true);
ERROR:  duplicate key value violates unique constraint "foo_i"

#8 楼

这种问题是我问这个问题的另一个原因：

数据库中的应用程序设置

如果数据库中有应用程序设置表，则可以输入引用要被视为“特殊”记录的一条记录的ID。然后，您只需从设置表中查找ID是什么，就不必为设置的一个项目而花费一整列。

这是一个很好的建议：它更符合规范化的设计，可与任何数据库平台一起使用，并且最容易实现。

–尼克·查玛斯（Nick Chammas）
2011年10月1日，下午1:44

+1，但请注意，“整列”可能不会使用任何物理空间，具体取决于您的RDBMS ：）

–杰克·道格拉斯（Jack Douglas）
2011-10-19 9:24

#9 楼

使用广泛实施的技术的可能方法：

1）撤消表上的“作者”特权。创建CRUD过程以确保约束在事务边界处得到实施。

2）6NF：删除CHAR(1)列。添加受约束的引用表，以确保其基数不能超过一个：

alter table foo ADD UNIQUE (bar);

create table foo_Y
(
 x CHAR(1) DEFAULT 'x' NOT NULL UNIQUE CHECK (x = 'x'), 
 bar int references foo (bar)
);

更改应用程序语义，以使所考虑的“默认”为新表中的行。

3）删除CHAR(1)列。添加一个seq整数列。对seq施加唯一约束。更改应用程序的语义，以使所考虑的“默认”是seq值为1或seq值为最大/最小值或类似值的行。可能使用视图来封装此逻辑。

#10 楼

对于使用MySQL的用户，这是一个合适的存储过程：

DELIMITER $$
DROP PROCEDURE IF EXISTS SetDefaultForZip;
CREATE PROCEDURE SetDefaultForZip (NEWID INT)
BEGIN
    DECLARE FOUND_TRUE,OLDID INT;

    SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
    IF FOUND_TRUE = 1 THEN
        SELECT ID INTO OLDID FROM PostalCode WHERE isDefault = TRUE;
        IF NEWID <> OLDID THEN
            UPDATE PostalCode SET isDefault = FALSE WHERE ID = OLDID;
            UPDATE PostalCode SET isDefault = TRUE  WHERE ID = NEWID;
        END IF;
    ELSE
        UPDATE PostalCode SET isDefault = TRUE WHERE ID = NEWID;
    END IF;
END;
$$
DELIMITER ;

要确保您的表是干净的并且该存储过程正常工作，并假定默认ID为200 ，请执行以下步骤：

ALTER TABLE PostalCode DROP INDEX isDefault_ndx;
UPDATE PostalCodes SET isDefault = FALSE;
ALTER TABLE PostalCode ADD INDEX isDefault_ndx (isDefault);
CALL SetDefaultForZip(200);
SELECT ID FROM PostalCodes WHERE isDefault = TRUE;

这也是一个对触发器也有帮助的触发器：

DELIMITER $$
CREATE TRIGGER postalcodes_bu BEFORE UPDATE ON PostalCodes FOR EACH ROW
BEGIN
    DECLARE FOUND_TRUE,OLDID INT;
    IF NEW.isDefault = TRUE THEN
        SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
        IF FOUND_TRUE = 1 THEN
            SELECT ID INTO OLDID FROM PostalCode WHERE isDefault = TRUE;
            UPDATE PostalCodes SET isDefault = FALSE WHERE ID = OLDID;
        END IF;
    END IF;
END;
$$
DELIMITER ;

确保您的表是干净的并且触发器可以正常工作（假定ID 200为默认值），请运行以下步骤：

DROP TRIGGER postalcodes_bu;
ALTER TABLE PostalCode DROP INDEX isDefault_ndx;
UPDATE PostalCodes SET isDefault = FALSE;
ALTER TABLE PostalCode ADD INDEX isDefault_ndx (isDefault);
DELIMITER $$
CREATE TRIGGER postalcodes_bu BEFORE UPDATE ON PostalCodes FOR EACH ROW
BEGIN
    DECLARE FOUND_TRUE,OLDID INT;
    IF NEW.isDefault = TRUE THEN
        SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
        IF FOUND_TRUE = 1 THEN
            SELECT ID INTO OLDID FROM PostalCode WHERE isDefault = TRUE;
            UPDATE PostalCodes SET isDefault = FALSE WHERE ID = OLDID;
        END IF;
    END IF;
END;
$$
DELIMITER ;
UPDATE PostalCodes SET isDefault = TRUE WHERE ID = 200;
SELECT ID FROM PostalCodes WHERE isDefault = TRUE;

尝试一下！

MySQL是否没有基于DRI的解决方案？只有代码？我很好奇，因为我开始越来越多地使用MySQL ...

– gbn
2011年8月19日下午14:01

#11 楼

在SQL Server 2000及更高版本中，您可以使用索引视图来实现复杂的（或多表）约束，例如您所要求的约束。
Oracle对于具有延迟检查约束的物化视图也具有类似的实现。 />
在这里查看我的帖子。

您能否在此答案中提供更多“内容”，例如一小段代码？现在，这只是几个一般性想法和一个链接。

–尼克·查玛斯（Nick Chammas）
2011-10-19 14:45

在这里举一个例子有点困难。如果单击链接，您将找到所需的“肉”。

–spaghettidba
2011年12月9日在8:33

#12 楼

标准过渡SQL-92，广泛实施，例如SQL Server 2000及更高版本：

从表中撤消“作者”特权。分别为WHERE chk = 'Y'和WHERE chk = 'N'创建两个视图，包括WITH CHECK OPTION。对于WHERE chk = 'Y'视图，包括一个搜索条件，使其基数不能超过一个。向视图授予“作者”特权。

视图的示例代码：

CREATE VIEW foo_chk_N
AS
SELECT *
  FROM foo AS f1
 WHERE chk = 'N' 
WITH CHECK OPTION

CREATE VIEW foo_chk_Y
AS
SELECT *
  FROM foo AS f1
 WHERE chk = 'Y' 
       AND 1 >= (
                 SELECT COUNT(*)
                   FROM foo AS f2
                  WHERE f2.chk = 'Y'
                )
WITH CHECK OPTION

即使您的RDBMS支持此功能，它也会疯狂地进行序列化，因此，如果您有多个用户，则可能会遇到问题

–杰克·道格拉斯（Jack Douglas）
2011年10月19日在9:19

如果多个用户同时进行修改，则他们必须排队（序列化）-有时可以，但通常不行（请考虑使用大量OLTP或进行长事务）。

–杰克·道格拉斯（Jack Douglas）
2011年10月19日，11：15

感谢您的澄清。我必须说，如果多个用户经常设置唯一的默认行，则设计选择（同一表中的标志列）是可疑的。

–有一天
2011-10-19 12:12

#13 楼

这是一个使用虚拟列的MySQL和MariaDB解决方案，更加优雅。它要求MySQL> = 5.7.6或MariaDB> = 5.2：

MariaDB [db]> create table foo(bar varchar(255), chk boolean);

MariaDB [db]> describe foo;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| bar   | varchar(255) | YES  |     | NULL    |       |
| chk   | tinyint(1)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果不想强制执行唯一约束，则创建一个为NULL的虚拟列：

MariaDB [db]> ALTER table foo ADD checked_bar varchar(255) as (IF(chk, bar, null)) PERSISTENT UNIQUE;

（对于MySQL，请使用STORED而不是PERSISTENT。）

MariaDB [db]> insert into foo(bar, chk) values('a', false);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> insert into foo(bar, chk) values('a', false);
Query OK, 1 row affected (0.01 sec)

MariaDB [salt_dev]> insert into foo(bar, chk) values('a', false);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> insert into foo(bar, chk) values('a', true);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> insert into foo(bar, chk) values('a', true);
ERROR 1062 (23000): Duplicate entry 'a' for key 'checked_bar'

MariaDB [db]> insert into foo(bar, chk) values('b', true);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> select * from foo;
+------+------+-------------+
| bar  | chk  | checked_bar |
+------+------+-------------+
| a    |    0 | NULL        |
| a    |    0 | NULL        |
| a    |    0 | NULL        |
| a    |    1 | a           |
| b    |    1 | b           |
+------+------+-------------+

#14 楼

标准FULL SQL-92：在CHECK约束中使用子查询，但未广泛实现，例如在ANSI-92查询模式下，在Access2000（ACE2007，Jet 4.0等）和更高版本中受支持。

示例代码：注意Access中的CHECK约束始终是表级别的。由于问题中的CREATE TABLE语句使用了行级CHECK约束，因此需要通过添加逗号对其稍作修改：

create table foo(bar int identity, chk char(1), check (chk in('Y', 'N')));

ALTER TABLE foo ADD 
   CHECK (1 >= (
                SELECT COUNT(*) 
                  FROM foo AS f2 
                 WHERE f2.chk = 'Y'
               ));

在我使用过的任何RDBMS中都不好...警告很多

–杰克·道格拉斯（Jack Douglas）
2011年10月19日在9:16

#15 楼

我只浏览了答案，所以我可能错过了类似的答案。这个想法是使用一个生成的列，该列要么是pk，要么是不存在的常量作为pk的值。

create table foo 
(  bar int not null primary key
,  chk char(1) check (chk in('Y', 'N'))
,  some_name generated always as ( case when chk = 'N' 
                                        then bar 
                                        else -1 
                                   end )
, unique (somename)
);

AFAIK这在SQL2003中是有效的（因为您正在寻找不可知论的解决方案）。 DB2允许它，但不确定有多少其他供应商接受它。

编程黑洞网