Ok. Thanks for the answer.

But the unique key must be combine with the check constraint. I mean:

1. If I have column1=ZZZ, column2=123, this can be repeated multiple times.

2. If I have column1=ZZZ, column2=123, column3=(11 OR 14 OR 15) must be unique.

Sorry If I did not explain as it should be.

Regards,

If you want conditional uniqueness (the combination of c1, c2, c3 must be unique only if c3 is 11, 14 or 15), you can accomplish it like this:

alter table t add
unique ( c1, c2, case when c3 in (11,14,15) then c3 else null end );

This works because rows are only indexed if all the indexed columns/expressions are not null.

Thanks for the answer.

It worked.

Regards,

Hello.

I executed the DDL:

alter table user1.table1 add
unique ( column1, column2, case when column3 in (11,14,15) then column3 else null end );

Error:
alter table user1.table1 add
unique ( column1, column2, case when column3 in (11,14,15) then column3 else null end )
Error at line 1
ORA-00904: : invalid identifier


The DDL was executed just fine creating an index:

CREATE UNIQUE INDEX user1.test_cons
ON user1.table1 ( column1, column2, CASE WHEN column3 IN (1,4,5) THEN column3 ELSE NULL END);

Index created

Data was coming to the table and the "constraint" had not been triggered. But when specific data came,

I got the error:

INSERT INTO "USER1"."TABLE1" (COLUMN1, COLUMN2, COLUMN3) VALUES ('123', 'doc1', '13')
ORA-00001: unique constraint (USER1.TEST_CONS) violated
ORA-06512: at line 1

One error saving changes to table "USER1"."TABLE1":
Row 4: ORA-00001: unique constraint (USER1.TEST_CONS) violated
ORA-06512: at line 1

Data currently in the database

COLUMN1 COLUMN2 COLUMN3
123 doc1 13
123 doc1 11
123 doc1 13 --> This record is not supposed to raise the constraint error. Because the value is NOT (11,14,15). Just with these values (11,14,15) the constraint must raised.

Thanks in advanced.

Regards,

Ok.

Here are the examples.

ID COLUMN1 COLUMN2 COLUMN3
1 123 doc1 14 --> This is good
2 123 doc1 15 --> This is good
3 123 doc1 12 --> This is good
4 123 doc2 12 --> This is good
5 123 doc1 10 --> This is good
6 123 doc1 11 --> This is good
7 123 doc2 11 --> This is good
7 123 doc2 16 --> This is good
8 123 doc1 11 --> This record must raised an error when inserting because there is a row (ID 6) with same value in COLUMN1 and COLUMN2 and value 11 in COLUMN3.
9 123 doc1 14 --> This record must raised an error when inserting because there is a row (ID 1) with same value in COLUMN1 and COLUMN2 and value 14 in COLUMN3.
10 123 doc1 15 --> This record must raised an error when inserting because there is a row (ID 2) with same value in COLUMN1 and COLUMN2 and value 15 in COLUMN3.

The constraint to be created must raise an error if we try to insert rows 8, 9 and 10 because of existing data.

1. There can be two records with same values in COLUMN1, COLUMN2 and if COLUMN3 NOT IN (11,14,15).

2. There can not be two records with same values in COLUMN1, COLUMN2 and COLUMN3 IN (11,14,15).

Thanks in advanced.

Regards,

Given this data set,

ID COLUMN1 COLUMN2 COLUMN3
1 123 doc1 14 --> This is good
2 123 doc1 15 --> This is good
3 123 doc1 12 --> This is good
4 123 doc2 12 --> This is good
5 123 doc1 10 --> This is good
6 123 doc1 11 --> This is good
7 123 doc2 11 --> This is good
7 123 doc2 16 --> This is good
8 123 doc1 11 --> This record must raised an error when inserting because there is a row (ID 6) with same value in COLUMN1 and COLUMN2 and value 11 in COLUMN3.
9 123 doc1 14 --> This record must raised an error when inserting because there is a row (ID 1) with same value in COLUMN1 and COLUMN2 and value 14 in COLUMN3.
10 123 doc1 15 --> This record must raised an error when inserting because there is a row (ID 2) with same value in COLUMN1 and COLUMN2 and value 15 in COLUMN3.

so when you say this
1. There can be two records with same values in COLUMN1, COLUMN2 and if COLUMN3 NOT IN (11,14,15).

then id=2 cannot be good, since it got column3=15 so its column1 and column2 duplicates with the row id=1.
simillary id=6 cannot be good, since it got column3=11 so its column1 and column2 duplicates with the row id=1.

demo@XEPDB1> create table t ( id int, column1 int, column2 varchar2(10), column3 int );
demo@XEPDB1> create unique index t_idx on t(
  2     case when column3 in (11,14,15) then column1 end,
  3     case when column3 in (11,14,15) then column2 end ) ;
demo@XEPDB1> insert into t(id,column1,column2,column3) values(1,123,'doc1',14);
demo@XEPDB1> insert into t(id,column1,column2,column3) values(2,123,'doc1',15);
insert into t(id,column1,column2,column3) values(2,123,'doc1',15)
*
ERROR at line 1:
ORA-00001: unique constraint (DEMO.T_IDX) violated


demo@XEPDB1> insert into t(id,column1,column2,column3) values(3,123,'doc1',12);
demo@XEPDB1> insert into t(id,column1,column2,column3) values(4,123,'doc2',12);
demo@XEPDB1> insert into t(id,column1,column2,column3) values(5,123,'doc1',10);
demo@XEPDB1> insert into t(id,column1,column2,column3) values(6,123,'doc1',11);
insert into t(id,column1,column2,column3) values(6,123,'doc1',11)
*
ERROR at line 1:
ORA-00001: unique constraint (DEMO.T_IDX) violated


demo@XEPDB1> insert into t(id,column1,column2,column3) values(7,123,'doc2',11);
demo@XEPDB1> insert into t(id,column1,column2,column3) values(8,123,'doc2',16);
demo@XEPDB1> insert into t(id,column1,column2,column3) values(9,123,'doc1',11);
insert into t(id,column1,column2,column3) values(9,123,'doc1',11)
*
ERROR at line 1:
ORA-00001: unique constraint (DEMO.T_IDX) violated


demo@XEPDB1> insert into t(id,column1,column2,column3) values(10,123,'doc1',14);
insert into t(id,column1,column2,column3) values(10,123,'doc1',14)
*
ERROR at line 1:
ORA-00001: unique constraint (DEMO.T_IDX) violated


demo@XEPDB1> insert into t(id,column1,column2,column3) values(11,123,'doc1',15);
insert into t(id,column1,column2,column3) values(11,123,'doc1',15)
*
ERROR at line 1:
ORA-00001: unique constraint (DEMO.T_IDX) violated


demo@XEPDB1> commit;
demo@XEPDB1> select * from t order by id ;

        ID    COLUMN1 COLUMN2       COLUMN3
---------- ---------- ---------- ----------
         1        123 doc1               14
         3        123 doc1               12
         4        123 doc2               12
         5        123 doc1               10
         7        123 doc2               11
         8        123 doc2               16
demo@XEPDB1>

Hello, Rajeshwaran.

then id=2 cannot be good, since it got column3=15 so its column1 and column2 duplicates with the row id=1.
simillary id=6 cannot be good, since it got column3=11 so its column1 and column2 duplicates with the row id=1.

You're right. If a row with values (11,14,15) already exist, the constraint must raise an error.

I will test your solution and let you know.

Thanks.

Regards,

Thanks. It worked.

Regards,