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

Question

We want to check unique data on a oracle table. But a value can represent multiple values for the unique constraint. This unique is over two columns, key and type. Key is just a string and type can contains three different values:

L, R and B which means L+R

+----+------+------+
| ID | Key  | Type |
+----+------+------+
| 1  | AAA  | L    |
| 2  | AAA  | R    |
| 3  | BBB  | B    |  B = L+R
| 4  | CCC  | L    |
| 5  | CCC  | B    |  Not possible because L and/or R exisits
| 6  | BBB  | L    |  Not possible because B exists
+----+------+------+

Is it possible to check this with a unique/check constraint?

Edit:

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.

I would like to try this, if possible, without a trigger.

Answer 1

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 

Answer 2

Thinking some more about your issue, I see the real answer here - your design is bad. If you can have L or R, or LR, it really means you can have only 1 value. In this case, you should have Key unique. And Type should be 1, 2 or 3. Just don't save second row but rather update existing value. Create table TypeValues

id Type
1   1
2   2
3   3

and make yourTable.Type a foreign key to your new table. And if you interfacing DB with some application, you create corresponding enum Types. In c# it will look like this

[Flags]
enum Types
{
    None = 0x0,
    L = 0x1,
    R = 0x2  
}

public static void Main()
{
    var x = Types.L | Types.R;

    Console.WriteLine((int)Types.L);  // Prints 1
    Console.WriteLine((int)Types.R);  // Prints 2
    Console.WriteLine((int)x);        // Prints 3
}