Re: Unique constraint and NULL values
Date: Wed, 1 Dec 2004 12:51:31 -0500
Message-ID: <col0bp02ok_at_enews1.newsguy.com>
Not sure anyone really answered this before they went off on the tangent.
I believe you can create an after-insert statement-level trigger (not for
each row) that CAN read the after-insert state of the subject table without
mutating.
So in that trigger, do something like:
"Agoston Bejo" <gusz1_at_freemail.hu> wrote in message
news:cl8ba7$d04$1_at_news.caesar.elte.hu...
var := 0 ;
select 1 into var from dual where exists (
select count(value), value from table
where value is not null
group by count(value)
having count(value) > 1 ) ;
-- then test var, if it is a 1, then raiserror cuz some non-null value was
in there twice or more.
>I want to enforce such a constraint on a column that would ensure that the
> values be all unique, but this wouldn't apply to NULL values. (I.e. there
> may be more than one NULL value in the column.)
> How can I achieve this?
> I suppose I would get the most-hated "table/view is changing,
> trigger/function may not see it" error if I tried to write a trigger that
> checks the uniqueness of non-null values upon insert/update.
>
>
Received on Wed Dec 01 2004 - 18:51:31 CET