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.

Leave a Reply

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