Unique Index not allowing to insert rows into table..

Kannan Sekar

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.

Frank Kulash

Hi, @Kannan Sekar

Is this thread a duplicate of: Unique Index not allowing to insert rows into table.. — oracle-tech

If so, one of them should be closed right away. It's confusing (and counter-productive) to have multiple threads for the same question. (This may not be your fault; the site can behave strangely.)

Frank Kulash

Hi,

I am unable to replicate the issue here, instead i explained in words below.

That makes it hard (perhaps impossible) to solve the problem. What problem do you have in producing a test case that the people who want to help you can replicate?

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

The table created above doesn't have a column called col1. If you have a problem with some table that has a column called col1, post the CREATE TABLE and INSERT statements for that table.

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

Explain the requirement that the index is supposed to meet. For example, "I need to make sure that among all the rows with the same acct_key value, no more than one of them has acc_status=1 and acct_grp_status=-1."

By the way, that index uses a CASE expression, not a CASE statement. A CASE statement is something different. You'll get better answers faster if you say what you mean.

Below kind of row value is not inserted in my real world (getting unique index error).

Are you sure the index you posted is causing the error? Post the exact, complete error message you get.