From: | Lew <noone(at)lewscanon(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Unique constraint on only some of the rows |
Date: | 2011-01-28 11:39:33 |
Message-ID: | ihu9tn$rh0$1@news.albasani.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
A B wrote:
>> If I want to create a table
>>
>> create table users (
>> id integer
>> name varchar(8)
>> enabled boolean
>> );
>>
>> and a constraint unique(id,name) but the unique constraint should
>> only be used for the enabled users, how can I do that?
>>
>> The only way I can think of is to use null values when a user is not
>> enabled and use the constraint unique(id,name,enable).
What is the meaning of the "id" column? It's unusual to see a column named
"id" that is part of a multi-column key; usually one sees it as a name for a
surrogate key.
I think you're modeling your tables wrong. You don't even have this one in
first normal form. Figure out the actual key column(s) and have "enabled" as
a dependent column only.
Thomas Kellerer wrote:
> You can create a unique index:
>
> create unique index idx_users
> on users (id, name)
> where enabled;
>
> Note there is a slight difference between a unique constraint and an unique
> index: the index cannot be used for foreign key reference (the unique
> constraint could). But that's the only difference as far as I know.
--
Lew
Ceci n'est pas une pipe.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Wood | 2011-01-28 13:55:33 | Re: Pgcrypto install (I've tried multiple ways) |
Previous Message | Basil Bourque | 2011-01-28 04:54:02 | Set default time zone for displaying dates in a session |