Re: PRIMARY KEY on a *group* of columns imply that each column is NOT

[Stephane Bortzmeyer <bortzmeyer@xxxxxx>]

On Wed, Apr 27, 2005 at 05:04:07PM +0200,
 Sebastian Böck <sebastianboeck@xxxxxxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster