I would like to implement a constraint for the following table:
CREATE TABLE GLOBAL_LOCKS
(
RESOURCE_NAME VARCHAR2(50) NOT NULL,
IS_EXCLUSIVE CHAR(1) DEFAULT 'N' NOT NULL
)
as follows:
1. RESOURCE_NAME must be unique if IS_EXLUSIVE = 'Y'
2. RESOURCE_NAME can be duplicate if all of them have 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
The idea is that exclusive and shared locks can be maintained. There can be only one exclusive lock for a given resource; but there can be multiple shared locks for a given resource.
This question is similar to
https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-enforce-conditional-unique-on-multiple-columns
I've used an index to satisfy the single "Y" part, because that gives immediate feedback. For the Y|N combinations, then it means we need to wait until committing so we can use an mview for that
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>