If one column is not null then another column should be not null, both can be nulls
ravi, June 27, 2008 - 9:24 am UTC
Thanks Tom, you always rock !!
I was thinking in the unique index direction. Just for learning, is it possible to implement the same using a unique index ?
Long live "Tom Kyte".
Thanks,
Ravi.
June 27, 2008 - 9:29 am UTC
using a unique index for this would not make sense - I don't see any possible connection between the rule:
c1 and c2 are either
both null
both not null
and the concept of
uniqueness
even if it "could be done", it would be the wrong way - we need not store anything extra here, we just need this rule to fire on the row - which it is - and we are done.
If one column is not null then another column should be not null, both can be nulls
ravi, June 27, 2008 - 9:48 am UTC
Hi Tom,
(sorry, overlooked the following before posting the earlier reponse).
"Also the combination of col1, col2 should be unique."
INSERT INTO t (col1, col2) VALUES ('X', 'X')
> 1 rows inserted
INSERT INTO t (col1, col2) VALUES ('X', 'X')
> 1 rows inserted
Please advise.
Thanks,
Ravi
June 27, 2008 - 9:59 am UTC
that is just then
alter table t add constraint c1_c2_unique unique(c1,c2);
If one column is not null then another column should be not null, both can be nulls
ravi, June 27, 2008 - 10:36 am UTC
Thanks Tom, much appreciated.
Long live "Tom Kyte".