Oracle 条件复杂约束

我想为下表实现一个约束:

CREATE TABLE GLOBAL_LOCKS 
( 
  RESOURCE_NAME VARCHAR2(50) NOT NULL, 
  IS_EXCLUSIVE CHAR(1) DEFAULT 'N' NOT NULL 
)

如下:
1.如果IS_EXLUSIVE = 'Y'，则RESOURCE_NAME必须唯一
2.如果所有资源都具有IS_EXCLUSIVE = 'N'，则资源名称可以重复

--Example 1
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'Y');
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
-- should fail

--Example 2
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
-- should work

--Example 3
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'Y');
-- should fail

这个想法是可以维护独占锁和共享锁。给定资源只能有一个排他锁; 但是给定资源可以有多个共享锁。

这个问题类似于https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-enforce-conditional-unique-on-multiple-columns

我使用索引来满足单个 “Y” 部分，因为它会立即提供反馈。对于Y | N组合，这意味着我们需要等到提交，这样我们就可以使用mview

SQL> CREATE TABLE GLOBAL_LOCKS
  2  ( pk int primary key,
  3    RESOURCE_NAME VARCHAR2(50) NOT NULL,
  4    IS_EXCLUSIVE CHAR(1) DEFAULT 'N' NOT NULL
  5  );

Table created.

SQL>
SQL> create unique index GLOBAL_LOCKS_IX
  2  on GLOBAL_LOCKS ( case when IS_EXCLUSIVE = 'Y' then RESOURCE_NAME end );

Index created.

SQL>
SQL>
SQL> create materialized view log on GLOBAL_LOCKS
  2    with rowid (resource_name, IS_EXCLUSIVE, pk) including new values
  3  /

Materialized view log created.

SQL>
SQL> create materialized view GLOBAL_LOCKS_CHECKER
  2  refresh fast
  3  on commit as
  4  select
  5    RESOURCE_NAME,
  6    count(*) c,
  7    count(decode(IS_EXCLUSIVE,'N',1)) n_cnt,
  8    count(decode(IS_EXCLUSIVE,'Y',1)) y_cnt
  9  from  GLOBAL_LOCKS
 10  group by RESOURCE_NAME;

Materialized view created.

SQL>
SQL> alter table GLOBAL_LOCKS_CHECKER add constraint GLOBAL_LOCKS_CHECKER_CHK
  2   check ( y_cnt = 0 );

Table altered.

SQL>
SQL> -- should fail
SQL> delete GLOBAL_LOCKS;

0 rows deleted.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (10,'MY_RESOURCE', 'Y');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (11,'MY_RESOURCE', 'Y');
INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (11,'MY_RESOURCE', 'Y')
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.GLOBAL_LOCKS_IX) violated


SQL>
SQL> -- should fail
SQL> delete GLOBAL_LOCKS;

1 row deleted.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (10,'MY_RESOURCE', 'Y');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (11,'MY_RESOURCE', 'N');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (MCDONAC.GLOBAL_LOCKS_CHECKER_CHK) violated


SQL> select * from GLOBAL_LOCKS_CHECKER;

no rows selected

SQL>
SQL> -- should be ok
SQL> delete GLOBAL_LOCKS;

0 rows deleted.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (21,'MY_RESOURCE', 'N');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (22,'MY_RESOURCE', 'N');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from GLOBAL_LOCKS_CHECKER;

RESOURCE_NAME                                               C      N_CNT      Y_CNT
-------------------------------------------------- ---------- ---------- ----------
MY_RESOURCE                                                 2          2          0

1 row selected.

SQL>
SQL> --should fail
SQL> delete GLOBAL_LOCKS;

2 rows deleted.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (31,'MY_RESOURCE', 'N');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (32,'MY_RESOURCE', 'N');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (33,'MY_RESOURCE', 'Y');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (MCDONAC.GLOBAL_LOCKS_CHECKER_CHK) violated


SQL> select * from GLOBAL_LOCKS_CHECKER;

RESOURCE_NAME                                               C      N_CNT      Y_CNT
-------------------------------------------------- ---------- ---------- ----------
MY_RESOURCE                                                 2          2          0

1 row selected.

SQL>
SQL>
SQL>