CODEWITHSUNDEEP
oracle-database
Gayathri Rao
Gayathri Rao

Reputation: 3

Conditional Constraint on field level

I have a table 'TEST' with 2 columns as shown below :

create table TEST (
SEQ_NUM NUMBER NOT NULL,
SWITCH VARCHAR2(1))

The switch column receives input as either 0 or 1 from the Application. For a particular SEQ_NUM value we can have any number of rows with Switch value 0. But for a particular SEQ_NUM value, we can only have one record with SWITCH value 1.

I have tried creating such constraint, but it is throwing syntax errors:

CREATE UNIQUE INDEX EMP_INDEX
ON TEST(
(CASE
     WHEN SWITCH = 1
         THEN UNIQUE (SEQ_NUM)        
     ELSE
         NULL
 END));

To be more clear, the output should be as below:

SQL> insert into TEST(SEQ_NUM,SWITCH) values (3,0);
SQL> 1 row created.

SQL> insert into TEST(SEQ_NUM,SWITCH) values (3,0);
SQL> 1 row created.

SQL> insert into TEST(SEQ_NUM,SWITCH) values (3,1);
SQL> 1 row created.

SQL> insert into TEST(SEQ_NUM,SWITCH) values (3,1);

ORA-error : query insertion failed with xyz error ---- query should throw error if I insert second time.

Upvotes: 0

Views: 35

Answers (1)

user330315
user330315

Reputation:

Remove the UNIQUE from the CASE expression:

CREATE UNIQUE INDEX EMP_INDEX
  ON TEST(CASE WHEN SWITCH = 1 THEN SEQ_NUM ELSE NULL END);

Online example: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=d3ac889aa5127bb2511cb4db611f4981

Upvotes: 3

Related Questions