• 首页
• 数据库
• 具有三列但只有一行可以等于某个值的Oracle约束

# 具有三列但只有一行可以等于某个值的Oracle约束 [英] Oracle Constraint with three columns, but only one row can equal a certain value

### 问题描述

``````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...
``````

``````ADD CONSTRAINT constratin_name_uq UNIQUE (room_id, name, status)
``````

``````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;
``````