I am using Oracle 11g version and I try to understand how the below unique index is working during INSERT. I am unable to replicate the issue here, instead i explained in words below.
Step1: Creating table with primary key on col1
CREATE TABLE T1(PKEY NUMBER(10) PRIMARY KEY, ACCT_NO NUMBER(10), ACCT_KEY NUMBER(10), ACCT_STATUS NUMBER(1), ACCT_GRP_STATUS NUMBER(1));
Step2: Creating unique index on using CASE statement
CREATE UNIQUE INDEX IDX_T1_ACCT_KEY ON T1 ( CASE WHEN ACCT_STATUS = 1 AND ACCT_GRP_STATUS = -1 THEN ACCT_KEY ELSE NULL END);
Step3: First 3 rows inserted successfully
INSERT INTO T1 (PKEY, ACCT_NO, ACCT_KEY, ACCT_STATUS, ACCT_GRP_STATUS) VALUES (101, 123456, 99999, 1, -1);
INSERT INTO T1 (PKEY, ACCT_NO, ACCT_KEY, ACCT_STATUS, ACCT_GRP_STATUS) VALUES (102, 123456, 99999, 0, -1);
INSERT INTO T1 (PKEY, ACCT_NO, ACCT_KEY, ACCT_STATUS, ACCT_GRP_STATUS) VALUES (103, 123456, 99999, -1, -1);
-- Below kind of row value is not inserted in my real world (getting unique index error). The col1 value 104 is not present in my table t1. As per case statement used in Index it will return as NULL. so why it not allowed to insert my rows into my real world table.
INSERT INTO T1 (PKEY, ACCT_NO, ACCT_KEY, ACCT_STATUS, ACCT_GRP_STATUS) VALUES (104, 123456, 99999, 1, 1);
Please suggest your view and any debugging ways to find the issue in my real world table.