Re: UNIQUE columnt depdening on other column???

On Wed, Jun 02, 2004 at 02:39:45PM +0300, Andrei Bintintan wrote:
> Hi,
>
> I have a problem.
>
> Let's say I have the following table:
> CREATE TABLE rekl_element(
> id serial PRIMARY KEY,
> active boolean NOT NULL DEFAULT 'y',
> num int4 NOT NULL,
> text varchar(10)
> );
>
> Now I want that "num" column is "unique" but only for those columns that
> have active='y'. For the columns that have active='f' I don't care if num is
> unique or not. I'm asking this because num will be doubled some times.

You can use a little trick for this.
Add a column 'uniqueness' that has a default nextval ('mysequence'::text).
Make an unique index on the columns (num, uniqueness).
Obviously, this will work because the column uniqueness has unique values.

Now write a trigger that sets the uniqueness column to 0 when the active
column equals 'y'. This will result in:

- unique num columns (or the index will fail) where active = 'y'
- arbitrary num colums (index will always be unique) where active = 'n'

--
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen reinoud(dot)v(at)n(dot)leeuwen(dot)net
http://www.xs4all.nl/~reinoud
__________________________________________________