Re: Multi-Column Constraints and Null Values

On Thu, 27 Apr 2006, Phillip Tornroth wrote:

> I have a problem in a number of my tables. I'd like to add uniqueness 
> constraints across multiple columns, but some of the columns are nullable. 
> This gives me trouble since when I create a constraint on columns A and B.. 
> I'd like the constraint to be enforced such that you couldn't insert values 
> ("value for A", null) twice. I understand why the constraints work this way, 
> but I'm wondering if anyone knows of a workaround.
>

Phill,

You likely want a multicolumn unique index created like so:

CREATE UNIQUE INDEX foo_A_B_unique_idx ON foo (A,B);

See the docs here: 
http://www.postgresql.org/docs/current/interactive/indexes-unique.html


-- 
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954