Re: unique constraint with a null column?

--- Bruno Wolff III <bruno(at)wolff(dot)to> wrote:

> On Fri, Dec 30, 2005 at 13:30:40 -0800,
> CSN <cool_screen_name90001(at)yahoo(dot)com> wrote:
> > I have three columns, and one of them can be null.
> I'd
> > like to create a unique constraint across all
> three
> > columns and allow only one null value. e.g.
> >
> > a|b|c
> > abc|123|null
> > abc|123|null # not allowed
> > abc|456|null
> > abc|456|987
> > abc|456|876
> > def|456|null
> > def|456|null # not allowed
> >
> > Currently, the 'not allowed' lines are allowed.
>
> That is how 'unique' constraints are supposed to
> work. One possible
> solution is to use some normal value instead of
> 'NULL' to represent
> that fact.
>

I know ;). 'c' is actually 'parent_id' with a f/k
contraint, so something like '0' wouldn't work. Hmm,
IIRC indexes can have WHERE clauses - perhaps I can
create a unique index that way...

csn

__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com