Re: Unique constraint on only some of the rows

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.