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

In article <20050426203938.GA18628@nic.fr>,
Stephane Bortzmeyer <bortzmeyer@nic.fr> wrote:

% But it does not apply to primary keys containing a group of
% columns. In that case (my case), columns do not have to be UNIQUE. But
% they have to be NOT NULL, which puzzles me.

It does apply to primary keys containing groups of columns.

You can get the table definition you want by using a unique constraint,
but you should know that in SQL, unique constraints don't apply to
rows containing null values in the constrained columns. If you
do this:

 create table x (
   name TEXT NOT NULL,
   address INET,
   CONSTRAINT na UNIQUE (name, address)
 );

your table definition will be as you want it, but the constraint you
want won't be there.

$ INSERT INTO x VALUES ('alpha');
INSERT 194224 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194225 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194226 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194227 1

--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com