3

I have a table with with 3 columns: A (number), B (number) and C (boolean).

I would need to create a rule that would prevent records from being created with columns A and B and with C equal to true. For example.

This would be allowed:

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

But this, no:

A  B  C
1  2  true
1  2  true
1  2  false
deldev
  • 1,296
  • 18
  • 27
  • Creating a primary key with the three columns does not resolve? – Lawrence Jul 26 '19 at 15:14
  • @Lawrence, creating a primary key with three columns will not allow me to create records like the 1st example cited. – deldev Jul 26 '19 at 15:16
  • 1
    I think Oracle doesn't allow you create a boolean collumn in the table.But you can try it: https://stackoverflow.com/questions/26679831/how-to-use-oracle-check-constraints-to-limit-number-of-registration – Lawrence Jul 26 '19 at 15:24
  • Encode c=true as c=0 , c=false as c <>0. Insert into c 0 for true, sequence.nextval for false. – Serg Jul 26 '19 at 15:24

2 Answers2

5

Use unique function based index, that process only rows with C = 'true'.

You must somehow combine the columns Aand B - I use the string concatenation.

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

insert into test(a,b,c) values (1,2,'true');
insert into test(a,b,c) values (1,2,'false');
insert into test(a,b,c) values (1,2,'false');
insert into test(a,b,c) values (1,2,'true');
ORA-00001: unique constraint (DWH.UQ_TRUE) violated

select * from test;

         A          B C        
---------- ---------- ----------
         1          2 true       
         1          2 false      
         1          2 false
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • I was about to write up something with using a virtual column to store TRUE or NULL and base the index on that column, but this is cleaner and neater. Nice! – saritonin Jul 26 '19 at 15:30
  • 1
    Forgive me, but instead of Boolean, the column C is a number. But the command worked for me. Thank you. – deldev Jul 26 '19 at 15:35
5

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...

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    @MarmiteBomber - your version is still fine with integers, which is all that's shown in the question *8-) – Alex Poole Jul 26 '19 at 15:36