Re: Unique constraint on only some of the rows

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