Re: Unique constraint and NULL values

"hankr" <hankr_at_skillview.com> wrote in message news:<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:
> 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.
> >
> >

Mark Stock presented the answer that same day (ah, the occasional advantage of reading from GOOGLE).

The answer (to be repetitious) is to use the UNIQUE CONSTRAINT on the column. It has precisely the behavior desired. No triggers need apply for this job.

HTH,
 ed Received on Thu Dec 02 2004 - 19:36:33 CET