Constraints

The other day a friend called me with a question on constraints. “I have a table where the combination of two columns must be unique”. My response was: “Use a Unique Constraint on these two columns”.
That should work, however…

“There is one value for the first column, where the second column doesn’t have to be unique.”
Let’s start with a simple script to play with this issue:

prompt create a simple table
create table t
( v1 number
, v2 number
)
/
prompt add a unique constraint on columns v1 and v2
alter table t add constraint uk_2cols unique (v1, v2)
/
prompt add some data
begin
  insert into t(v1, v2) values (1,1);
  insert into t(v1, v2) values (1,2);
  insert into t(v1, v2) values (2,1);
  insert into t(v1, v2) values (2,2);
  insert into t(v1, v2) values (3,1);
  commit;
end;
/

If we add a duplicate record, this will fail:

prompt try to add a duplicate, this will fail
insert into t(v1, v2) values (3,1)
/

So using a Unique Constraint like this doesn’t work. There is a solution to this using a package and some triggers to work around a mutating table problem, but there should be a solution just using SQL and the features of the Oracle Database.

So we drop the constraint, it doesn’t do what we want.

prompt drop the constraint
alter table t drop constraint uk_2cols
/

Remember, two (or more) NULL values are considered to be Unique, so I create a virtual column in the table with the following statement:

prompt add a virtual column
alter table T add v3 as (case when v1 = 3 then null else v1 || v2 end)
/

Then we can add a unique constraint on the virtual column, which now contains NULL values for the values that don’t have to be unique

prompt add a unique constraint on this virtual column
alter table t add constraint uk_2cols unique (v3)
/

If we now try the insert again, this works.

prompt now the insert works
insert into t(v1, v2) values (3,1)
/

But the insert where the combination should be unique still failes.

prompt but this one still failes
insert into t(v1, v2) values (1,1)
/

As you can see, you can solve this issue by just using the features provided by the Oracle Database.

4 thoughts on “Constraints

  1. Hi Patrick,

    nice example.
    I noticed the expression for calculating the virtual column needs an enhancement. My argument is that two sets of different numbers may produce the same concatenated result. Like (1,11) and (11,1)….
    Adding a comma-symbol would solve that:
    alter table T add v3 as (case when v1 = 3 then null else to_char(v1)||’,’||to_char(v2) end)
    /


    1. Nice catch Rob
      I only gave the friend an idea. Didn’t say it was completely fool proof. 😉
      Thanks for the enhancement though 🙂


  2. … case when v1 = 3 then null else v1 || v2 end …

    .. beim CONCAT mal immer einen Trenner vorsehen sonst explodiert ungewollt

    1 und 12 als 112 versus 11 und 2 als 112



Leave a Reply to Patch72 Cancel reply

Your email address will not be published. Required fields are marked *