Conditional Unique Constraint SQL

Question

Is there a way to ignore a unique constraint if a given condition is true?

For example I got 3 columns in my database that form a unique constraint:

create table example_table
  (
    column_primarykey RAW(16) default NULL not null,
    column_a number(8) not null,
    column_b number(8) not null,
    column_c number(8) not null,
    constraint constraint_1
          unique(column_a, column_b, column_c)
    constraint constraint_2
          primary key (column_primarykey)

Now I add a fourth column:

alter table example_table
      add column_d number(8) not null,

What I want to achieve is, that the unique constraint gets ignored if the value of column_d already exists in the table. If column_d is not unique in the table the unique constraint gets ignored and you can add the row to the table. For example this is the existing data in my table (ignoring primary key cause not relevant):

column_a	column_a	column_c	column_d
1	2	3	1
3	4	5	2

So want I want is that you can add for example (1, 2, 3, 1) but not (1, 2, 3, 2) since there is already a row with the first three values. It should be only possible if the value in column_d already exists and the other values are equal to the existing row.

More examples to help with understanding:

Example insert	result	reason
(1, 2, 3, 1)	accepted	d is not unique and a, b, c got same values as the existing row with value 1 for column_d
(1, 2, 3, 4)	rejected	a, b ,c exists already in the table
(5,6,7,1)	rejected	1 exists but with different values for a b and c
(3,4,5, 2)	accepted	d exists and a, b, c, have the same values
(7,8,9,3)	accepted	a, b, c are unique and d does not exist

Answer 1

As far as I understood you have two unique constraints (on A,B,C and on D) and you want to suppress both if you have duplicate on A,B,C,D.

This is AFAIK not possible to do in one table, so you must split your setup in two tables.

The first one ABCD check the constraints, but do not allow duplicates, the second one TAB stores the actual data and refrence the first table.

In a before insert trigger the unique values on A,B,C,D are merged in the first table.

create table abcd
(a int,
 b int,
 c int,
 d int,
 constraint abcd unique (a,b,c,d),
 constraint abc unique (a,b,c),
 constraint d unique(d));

drop  table tab;
create table tab
(pk int,
 a int,
 b int,
 c int,
 d int, 
 primary key(pk),
 foreign key(a,b,c,d) references abcd(a,b,c,d)
 );
 
CREATE OR REPLACE TRIGGER tab_trigger
  BEFORE  INSERT ON tab
  FOR EACH ROW
BEGIN
   merge into abcd using
   (select  :new.a a, :new.b b, :new.c c, :new.d d from dual) src
    on  (abcd.a = src.a and abcd.b = src.b and abcd.c = src.c and abcd.d = src.d)
    when not matched then insert (a,b,c,d) values (src.a, src.b, src.c, src.d)
    ;
END;
/

Test runs as expected

insert into tab(PK, A, B, C, D) values (1, 1,2,3,1);
1 row inserted.
insert into tab(PK, A, B, C, D) values (2, 3,4,5,2);
1 row inserted.
insert into tab(PK, A, B, C, D) values (3, 1,2,3,1);
1 row inserted.
insert into tab(PK, A, B, C, D) values (4, 1,2,3,4);
ORA-00001: unique constraint (ZZZ.ABC) violated
insert into tab(PK, A, B, C, D) values (5, 5,6,7,1);
ORA-00001: unique constraint (ZZZ.D) violated
insert into tab(PK, A, B, C, D) values (6, 3,4,5,2);
1 row inserted.
insert into tab(PK, A, B, C, D) values (7, 7,8,9,3);
1 row inserted.

Anyway I must confes I do not like trigger based solutions, what I'd prefere would be a postponed validation with a check view.

I.e. you can insert any rows, but in the view you see which ones are invalid and can you handle it.

The validation query is rather straightforeward and it uses the analytic function to get the duplication count of the keys.

with abcd as (
select PK, A, B, C, D,
count(*) over (partition by A, B, C order by  PK) cnt_abc,
count(*) over (partition by D order by  PK) cnt_d,
count(*) over (partition by A, B, C, D order by PK) cnt_abcd
from tab)
select
   PK, A, B, C, D, CNT_ABC, CNT_D, CNT_ABCD,
   case when (CNT_ABC > 1 or CNT_D > 1) and CNT_ABCD = 1 then 'rejected' 
   else 'accepted' end as status
from abcd   
order by PK;

        ID          A          B          C          D    CNT_ABC      CNT_D   CNT_ABCD STATUS  
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
         1          1          2          3          1          1          1          1 accepted
         2          3          4          5          2          1          1          1 accepted
         3          1          2          3          1          2          2          2 accepted
         4          1          2          3          4          3          1          1 rejected
         5          5          6          7          1          1          3          1 rejected
         6          3          4          5          2          2          2          2 accepted
         7          7          8          9          3          1          1          1 accepted

Answer 2

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?

a	b	c		d		x	y	z
1	2	3		1		1	3	1
1	2	3		1		2	8	7
1	2	3		1		5	9	2
4	5	6		2		9	8	7
4	5	6		2		4	5	6
4	5	6		2		3	2	1
4	5	6		2		2	1	0

Databases are not spreadsheets or flat files though, they're relational structures.

The file above would probably be represented better in a database as two tables...

a	b	c		d
1	2	3		1
4	5	6		2

d		x	y	z
1		1	3	1
1		2	8	7
1		5	9	2
2		9	8	7
2		4	5	6
2		3	2	1
2		2	1	0

If you want a new row of "data", you add a row to the second table.

If you want to create a new relationship between (a,b,c) and (d), you add a row to the first table.

Which can be implemented and enforced as follows...

CREATE TABLE map (
    column_a       NUMBER(8) NOT NULL,
    column_b       NUMBER(8) NOT NULL,
    column_c       NUMBER(8) NOT NULL,
    column_d       NUMBER(8) NOT NULL,
    UNIQUE(column_a, column_b, column_c),
    UNIQUE(column_d)
)

CREATE TABLE fact (
    column_pk      RAW(16)   NOT NULL,
    column_d       NUMBER(8) NOT NULL,
    column_x       NUMBER(8) NOT NULL,
    column_y       NUMBER(8) NOT NULL,
    column_z       NUMBER(8) NOT NULL,
    PRIMARY KEY (column_pk),
    FOREIGN KEY (column_d) REFERENCES map(column_d)
)

As far as I can tell, this structure can contain everything what you want to allow, and disallow everything you want to disallow.