Oracle Unique Constraint Based on Column Value

Oracle Unique Constraint based on column value

You can create a unique function-based index

CREATE UNIQUE INDEX idx_dup_active
    ON <<table name>>( CASE WHEN is_active = 1
                            THEN question_no
                            ELSE NULL
                        END );

This takes advantage of the fact that Oracle b-tree indexes do not store data where the leaf block data would be entirely NULL.

Oracle: unique constraint on column with value, which represents multiple values

If possible, consider re-modelling. Create a new table. Use the old table's key column, and apply a PK constraint (which will enforce uniqueness and NOT NULL). Have a column for each of the (sub)types you are dealing with (L,R). Use a CHECK constraint that allows only one-letter abbreviations representing the (sub)types. Include a virtual column that will "contain" the letter 'B' if both subtype columns are filled. DDL code:

create table kt2 ( 
  key varchar2( 64 ) primary key
, typeL varchar2( 1 )
, typeR varchar2( 1 )
, typeB varchar2( 1 ) generated always as (
    case when typeL = 'L' and typeR = 'R' then 'B' else null end
  ) virtual
, constraint types_check check (
    ( typeL = 'L' and typeR = 'R' )
    or
    ( typeL = 'L' and typeR is null )
    or
    ( typeL is null and typeR = 'R' )  
  )  
) ;

Testing

DBfiddle

insert into kt2 ( key, typeL ) values ( 'AAA', 'L' ) ;

SQL> select * from kt2 ;
KEY  TYPEL  TYPER  TYPEB  
AAA  L      NULL   NULL 

-- fails (key value must be unique), needs update
insert into kt2 ( key, typeR ) values ( 'AAA', 'R' ) ;

update kt2 set typeR = 'R' where key = 'AAA' ;

SQL> select * from kt2;
KEY  TYPEL  TYPER  TYPEB  
AAA  L      R      B  

-- cannot insert into B ("generated")
insert into kt2 ( key, typeB ) values ( 'BBB', 'B' ) ;
-- ORA-54013: INSERT operation disallowed on virtual columns

If you decide to go down this route, you can transfer all the data stored in the old table (name here: KT) to the new table like this:

insert into kt2 ( key )
select unique key from kt -- KT: the old table ;

update kt2
set typeL = 'L'
where key = ( select key from kt where key = kt2.key and type = 'L' )
;

update kt2
set typeR = 'R'
where key = ( select key from kt where key = kt2.key and type = 'R' )
;

EDIT (after question update)

Requirements added to the original question:

Together with this, additional data is saved. L and R can have
different data. B is in case that L and R are the same. So only one
row is saved.

New suggestion:

Table and constraints

create table kt2 ( 
  id number generated always as identity start with 1000 primary key
, key varchar2( 64 ) 
-- columns for values of type L
, L1 varchar2( 3 ), L2 varchar2( 3 ), L3 varchar2( 3 )
-- columns for values of type R
, R1 varchar2( 3 ), R2 varchar2( 3 ), R3 varchar2( 3 )
-- values for types L and R are identical -> type B
, typeB varchar2( 1 ) generated always as (
    case when L1 = R1 and L2 = R2 and L3 = R3  then 'B' else null end
  ) virtual
, constraint key_typeL_unique unique ( key, L1, L2, L3  ) 
, constraint key_typeR_unique unique ( key, R1, R2, R3  ) 
) ;

Testing

-- testing: AAA has attribute values for type L and for type R
-- type: L
insert into kt2 ( key, L1, L2, L3 )
  values ( 'AAA', 11, 12, 13 ) ;
-- type: R
insert into kt2 ( key, R1, R2, R3 )
  values ( 'AAA', 51, 52, 53 ) ;

-- type B: L and R "are the same" 
insert into kt2 ( key, L1, L2, L3, R1, R2, R3 )
  values ( 'BBB', 14, 15, 16, 14, 15, 16) ;
-- type: L
insert into kt2 ( key, L1, L2, L3 )
  values ( 'CCC', 17, 18, 19 ) ;

-- key CCC, type L
-- insert not possible because L exists
insert into kt2 ( key, L1, L2, L3 )
  values ( 'CCC', 17, 18, 19  ) ;
-- ORA-00001: unique constraint (...KEY_TYPEL_UNIQUE) violated

-- key BBB type L
-- Not possible because B exists
insert into kt2 ( key, L1, L2, L3 )
  values ( 'BBB', 14, 15, 16 ) ;
-- ORA-00001: unique constraint (...KEY_TYPEL_UNIQUE) violated

After the inserts, the table contains ...

SQL> select * from kt2;
ID    KEY  L1    L2    L3    R1    R2    R3    TYPEB  
1000  AAA  11    12    13    NULL  NULL  NULL  NULL   
1001  AAA  NULL  NULL  NULL  51    52    53    NULL   
1002  BBB  14    15    16    14    15    16    B      
1003  CCC  17    18    19    NULL  NULL  NULL  NULL 

Oracle Unique constraint on certain value

I would add virtual invisible column is_active:

alter table ip_list
   add is_active varchar2(1) 
       invisible 
       generated always as 
          (case when test_STATUS=1 then 'y' end) virtual;

alter table ip_list add constraint uq_active_ip unique (is_active);

Since it's invisible, it will not affect existing queries. You can get it only if you specify is_active in your query.

Full example:

SQL> create table ip_list(test_IP,test_STATUS)
  2  as
  3  select '1.2.3.4', 1 from dual union all
  4  select '1.2.3.5', 2 from dual union all
  5  select '1.2.3.6', 2 from dual union all
  6  select '1.2.3.7', 2 from dual ;

Table created.

SQL> alter table ip_list add is_active varchar2(1) invisible generated always as (case when test_STATUS=1 then 'y' end) virtual;

Table altered.

SQL> alter table ip_list add constraint uq_active_ip unique (is_active);

Table altered.

SQL> insert into ip_list(test_ip, test_status) values('1.2.3.8',1);
insert into ip_list(test_ip, test_status) values('1.2.3.8',1)
*
ERROR at line 1:
ORA-00001: unique constraint (XTENDER.UQ_ACTIVE_IP) violated

-- note that * does not return this new column:
SQL> select * from ip_list;

TEST_IP TEST_STATUS
------- -----------
1.2.3.4           1
1.2.3.5           2
1.2.3.6           2
1.2.3.7           2

-- but you can use it to filter active rows:
SQL> select * from ip_list where is_active='y';

TEST_IP TEST_STATUS
------- -----------
1.2.3.4           1

1 row selected.

How to give a unique constraint to a combination of a column and a fixed value in Oracle?

Slight variation on MarmiteBomber's approach, to avoid concatenating the values (which could cause accidental clashes with non-integer values):

create table t (a number, b number, c varchar2(5),
  constraint t_chk check (c in ('true', 'false'))
);

create unique index t_unq
on t (case when c = 'true' then a end, case when c = 'true' then b end);

insert into t(a,b,c) values (1,2,'true');

1 row inserted.

insert into t(a,b,c) values (1,2,'false');

1 row inserted.

insert into t(a,b,c) values (1,2,'false');

1 row inserted.

insert into t(a,b,c) values (1,2,'true');

ORA-00001: unique constraint (MY_SCHEMA.T_UNQ) violated

select * from t;

         A          B C    
---------- ---------- -----
         1          2 true 
         1          2 false
         1          2 false

---

Quick example of why non-integers (if they can exist) might be a problem:

create unique index uq_true on test(case when c = 'true' then a||'.'||b end);

insert into test(a,b,c) values (1.1, 2,'true');

1 row inserted.

insert into test(a,b,c) values (1, 1.2,'true');

ORA-00001: unique constraint (MY_SCHEMA.UQ_TRUE) violated

select * from test;

         A          B C    
---------- ---------- -----
       1.1          2 true 

... because for both '1.1' ||'.'|| '2' and '1' ||'.'|| '1.2' resolve to the same string, '1.1.2'.

This can also be a problem when combining string values rather than numbers. In either case you can avoid it by using a delimiter which cannot exist in either value; harder to do with strings, but with numbers any punctuation other than a period (or comma to be safe) would probably do - unless someone has a weird setting for nls_numeric_characters...

Unique constraint where only one row can be 'Yes' (but based on values in other columns)

I found the solution!

create unique index logo_active_uk
on logo_t(
  (case when Active = 'Yes' then SystemId || LogoType || Active else null end)
) tablespace t_indexes;

Oracle SQL create unique INDEX constraint based on status column and other 4 column

This is the 12th pseudo constraint (based on constraint types) that you can impose on a database model, that doesn't require the use of the clause CONSTRAINT to define it.

You can create a partial unique index to enforce it. For example:

create unique index ix1 on demo (
  case when status = 1 then col_1 end,
  case when status = 1 then col_2 end, 
  case when status = 1 then col_3 end,
  case when status = 1 then col_4 end,
  case when status = 1 then status end
);

See running example at db<>fiddle.

ORACLE: How to combine UNIQUE Constraint with other column?

If the combination is not allowed, then add a unique constraint or index:

ALTER TABLE Table_name 
  ADD CONSTRAINT Value_constraint UNIQUE (Value, Flag);

If you always want the flag to be upper case, then add a constraint to that effect as well:

ALTER TABLE Table_name 
  ADD CONSTRAINT CHK_Flag_Upper CHECK (Flag = UPPER(Flag));

Oracle UNIQUE constraint allows same value in different case

Since you are using Oracle 18 you can use new features like invisible columns and virtual columns or collation:

Virtual invisble column + unique constraint:
For example, you have a table T with column STR:

create table t(str varchar2(10));

So you can add invisible virtual column str_lower generated as lower(str):

alter table t add 
      str_lower varchar2(10) invisible generated always as (lower(str)) ;

Since this column is invisible and virtual, it will not break your existing code.
Now you can add unique constraint on it:

alter table t add
      constraint t_str_unique_lower
         unique(str_lower) using index;

Testing it:

SQL> insert into t values('M100');

1 row created.

SQL> insert into t values('m100');
insert into t values('m100')
*
ERROR at line 1:
ORA-00001: unique constraint (XTENDER.T_STR_UNIQUE_LOWER) violated

In addition it allows you to easily find values by lower value:

SQL> select * from t where str_lower='m100';

STR
----------
M100

SQL> select str,str_lower from t where str_lower='m100';

STR        STR_LOWER
---------- ----------
M100       m100

As you can see it doesn't return str_lower column if you not specify it in select-list:

Another possible solution is to specify collation for your column, but it requires to set database parameter MAX_STRING_SIZE to EXTENDED, otherwise you'll get ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

alter table t modify str COLLATE BINARY_CI;
alter table t add constraint t_str_unique unique(str);

More about this:
https://oracle-base.com/articles/12c/column-level-collation-and-case-insensitive-database-12cr2

Conditional Unique Constraint SQL

It sounds like you've tried to squash two or more tables in to one table.

• It's hard to tell without more background

For example, if you made a big flat file you might have this?

---