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

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.