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.
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)
Nice catch Rob
I only gave the friend an idea. Didn’t say it was completely fool proof. 😉
Thanks for the enhancement though 🙂
… 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
I think that is pretty much the same issue Rob also mentioned, but again: Nice Catch.