oracle - 具有三列的 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 约束,但在 value = 'ON' 的状态列上的 COUNT 不大于 1...以确保只有一个“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;
db<>在这里摆弄
推荐阅读
- php - WooCommerce - 如何在标签云中没有“库存”产品时隐藏产品标签
- php - WordPress 循环是否遍历每个帖子然后在找到当前帖子时停止?
- r - 有没有办法只保留白名单中字符串中定义的字符?
- sql - 过滤和比较 array_agg 值
- php - Laravel RedirectIfAuthenticated
- css - Node sass 没有在本地编译
- snowflake-cloud-data-platform - Snowflake ON_ERROR=CONTINUE 中止文件的 COPY 命令
- ios - 基于属性 bool 值写入访问权限的 Firestore 规则
- qnx - 使用 pidin 命令的处理器信息
- scopus - pybliometrics:.get_coauthors() 方法如何影响 API 配额?