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

In article <20050426203938(dot)GA18628(at)nic(dot)fr>,
Stephane Bortzmeyer <bortzmeyer(at)nic(dot)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(at)interlog(dot)com