Conditional Unique Constraint SQL

1
votes

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
2
sqloracleconstraintsunique-constraint
Which database are you using? Oracle and SQL Server, for example, would have different syntax and functionality. I also think you need to expand your examples a bit more. Show a larger number of cases where an insert would need to be rejected, and a large number of cases where an insert should be accepted. Then, the likely solution would involve a CHECK constraint instead of the UNIQUE constraint : docs.microsoft.com/en-us/sql/relational-databases/tables/… (Depending on which database you're using) - MatBailie
Not clear why (1, 2, 3, 2) can not be added? value 2 of column_d already exists in the table. - Serg
I am using Oracle. @Serg (1, 2, 3, 2) can not be added since (1, 2, 3, 1) is alreay in the database. If the value of column_d is equal the other values have to be equals aswell. I know this doesn't make sense but in my actual database I have more columns. I simplified it for my question since these are the relevant ones. But good catch, I forgot to mentioned that and maybe got confused myself. - knarc
Second to @MatBailie comment, more examples could help. - Serg
To paraphrase; inserts must be rejected if...Any rows already exist with the same (a,b,c) and different (d)? - MatBailie

2 Answers

1
votes

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.

1
votes

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