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.