Oracle 约束具有三列，但只有一行可以等于某个值

这听起来可能很简单，也可能很简单。我有 3 列，一列带有常量字符串（名称），一列（状态）带有两个选项（外键），另一列（room_id）是另一个表的父键，如何确保只设置一行相同名称的“ON”值。不能有多个具有相同名称的“ON”值的行，但可以有多个具有相同名称的“OFF”值的行。例子。

room_id       name       status
1             daniel       ON   --- OK for only one ON to be set for daniel
2             daniel       OFF
3             daniel       OFF
4             daniel       OFF
5             daniel       OFF
6             daniel       ON  --- THIS IS NOT ALLOWED...  but everything is UNIQUE
1             jeff         OFF
2             jeff         OFF
3             jeff         ON  --- OK for only ONE to be set to "ON" for jeff.
4             jeff         ON  --- THIS IS NOT ALLOWED... the room_id keeps things UNIQUE
5             jeff         ON  --- THIS IS NOT ALLOWED...

下面的 UNIQUE 约束允许多个具有相同名称的“ON”值。

ADD CONSTRAINT constratin_name_uq UNIQUE (room_id, name, status)

我相信我还需要一个 CHECK 约束，但状态列上的 COUNT 不大于 1，其中 value = 'ON'...以确保只有一个“NAME”和“STATUS”具有 ON 值... 。

下面的约束不允许有多个“OFF”值，这在我的例子中是需要的。

ADD CONSTRAINT constraint_name_uq UNIQUE (name, status)

另外...一切都可以“关闭”，但每个给定名称只能有一个“打开”。

任何帮助表示赞赏，

谢谢你， 丹尼尔

您可以创建唯一索引：

CREATE UNIQUE INDEX table_name__status_name__u
  ON table_name( CASE status WHEN 'ON' THEN name END );

其中，对于表：

CREATE TABLE table_name (
  room_id NUMBER
          NOT NULL,
  name    VARCHAR2(10)
          NOT NULL,
  status  VARCHAR2(3)
          NOT NULL
          CHECK ( STATUS IN ( 'ON', 'OFF' ) )
);

然后这有效：

INSERT INTO table_name ( room_id, name, status )
SELECT 1, 'alice', 'ON' FROM DUAL UNION ALL
SELECT 2, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 3, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 4, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 5, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 6, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 1, 'beatrice', 'OFF' FROM DUAL UNION ALL
SELECT 2, 'beatrice', 'OFF' FROM DUAL UNION ALL
SELECT 3, 'beatrice', 'OFF' FROM DUAL;

然而，这个：

INSERT INTO table_name ( room_id, name, status )
SELECT 7, 'alice', 'ON' FROM DUAL;

失败：

ORA-00001: unique constraint (FIDDLE_XFKAWDIVOXGJZVQESSZQ.TABLE_NAME__STATUS_NAME__U) violated

并且这也会失败并出现相同的异常：

INSERT INTO table_name ( room_id, name, status )
SELECT 4, 'beatrice', 'ON' FROM DUAL UNION ALL
SELECT 5, 'beatrice', 'ON' FROM DUAL;

数据库小提琴