Re: PRIMARY KEY on a *group* of columns imply that each column is NOT - Mailing list pgsql-general

On Wed, Apr 27, 2005 at 05:04:07PM +0200,
 Sebastian Böck <sebastianboeck@freenet.de> wrote
 a message of 24 lines which said:

               One is enough :-)
               vvvvv
> CREATE TABLE table x (
>   name TEXT NOT NULL,
>   address INET
> );
>
> CREATE UNIQUE INDEX na ON x (name, address);
> CREATE UNIQUE INDEX n ON x (name) WHERE address IS NULL;

Great! It works fine. Many thanks.

tests=> select * from x;
 name | address
------+---------
 foo  |
 foo  | 1.2.3.4
 foo  | ::1
 bar  | ::1
 bar  |
(5 rows)
tests=> insert into x (name) values ('bar');
ERROR:  duplicate key violates unique constraint "n"
tests=> insert into x (name, address) values ('bar', '::1');
ERROR:  duplicate key violates unique constraint "na"
tests=> insert into x (name) values ('new');
INSERT 21128 1