Re: Unique constraint and NULL values

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:
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.

"Agoston Bejo" <gusz1_at_freemail.hu> wrote in message news:cl8ba7$d04$1_at_news.caesar.elte.hu...
>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