Check/unique constraint question

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Check/unique constraint question
Date: 2006-03-05 08:25:58
Message-ID: Pine.LNX.4.64.0603050015050.29717@discord.dyndns.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a table with the following structure:

Column | Type | Modifiers
------------+---------+-----------------------
active | boolean | not null default true
id | integer | not null
(other columns left out)

And would like to make a unique constraint which would only check the
uniqueness of id if active=true.

So, the following values would be acceptable:

('f',5)
('f',5)
('t',5)

But these would not be:

('t',5)
('t',5)

Basically, I want something like:
ALTER TABLE bar ADD CONSTRAINT foo UNIQUE(active (where active='t'),id)

But the above does not appear to exist. Is there a simple way to create a
check constraint for this type of situation, or do I need to create a function
to eval a check constraint?

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Nikolay Samokhvalov 2006-03-05 09:02:58 Re: Check/unique constraint question
Previous Message Peter Ivarsson 2006-03-04 20:37:54 unsubscribe