Skip site navigation (1) Skip section navigation (2)

Re: unique constraint with a null column?

From: CSN <cool_screen_name90001(at)yahoo(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: unique constraint with a null column?
Date: 2005-12-30 21:40:55
Message-ID: 20051230214055.19650.qmail@web52901.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
--- 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 


In response to

pgsql-general by date

Next:From: Bruno Wolff IIIDate: 2005-12-30 21:41:33
Subject: Re: unique constraint with a null column?
Previous:From: CSNDate: 2005-12-30 21:30:40
Subject: unique constraint with a null column?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group