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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954