Thread: Unique constraint on only some of the rows

Hello there!

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).

Are there other ways of doing this?

A B, 24.01.2011 11:16:
> Hello there!
>
> 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).
>

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
keyreference (the unique constraint could). But that's the only difference as far as I know. 

Regards
Thomas

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.