Re: [SQL] database design SQL prob.

Currently, in postgreSQL, primary keys are created as a UNIQUE index on the
field(s) that form the primary key.

This means that there is no difference between explicitely declaring a
PRIMARY KEY in your table definition or using the CREATE UNIQUE INDEX
command.
There is one caveat to this, CREATE UNIQUE INDEX (at least in my PG 6.4.0)
will allow NULLs to be inserted in the indexed field (theoretically, all
NULLs could be different) whereas declaring that field as a primary key in
the table definition will ensure that no NULLs can be inserted (because if
there are several NULLs, you cannot use the field to uniquely identify an
entry).

So to have member_id as you primary key and ensure uniqueness of the
combination of firstname, lastname, adress, zipcode you get:

CREATE TABLE "member" (
"member_id" int4 DEFAULT nextval ( 'lid_id_seq' ) UNIQUE NOT NULL,
"firstname" text, -- NOT NULL? you must decide
"lastnaam" text, -- Ditto (typo? should it be lastname?)
"adress" text, -- Ditto (typo? should it be address?)
"zipcoder" character(4), -- Ditto
"telephone" text,
"email" text,
"registration_date" date DEFAULT current_date NOT NULL,
"student_id" text,
"dep_id" text,
"password" text NOT NULL,
"validated" bool DEFAULT 'f' NOT NULL,
PRIMARY KEY (member_id)
);

And then you create the unique index on the other fields:

CREATE UNIQUE INDEX member_fn_ln_ad_zc_idx ON member (firstname, lastnaam,
adress, zipcode);

You can get more info by typing \h create index and \h create table in psql.

Regards,

Stuart.

>The idea of the table below is to keep track of members. They have to register
>themself so I want to prevent them from subscribing twice. That's why I used a
>primary key on the fields firstname, lastname, adres, zipcode. But I would
>really want member_id to be my primary key as the table is referenced by other
>tables. Can I make firstname, lastname... a unique value in another way?
>Like constraint UNIQUE (firstname, lastname,adres,zipcode)
>I just made that last one up but is it possible to enforce the uniqueness of a
>couple of fields together?
>
>CREATE TABLE "member" (
> "member_id" int4 DEFAULT nextval ( 'lid_id_seq' ) UNIQUE NOT NULL,
> "firstname" text,
> "lastnaam" text,
> "adress" text,
> "zipcoder" character(4),
> "telephone" text,
> "email" text,
> "registration_date" date DEFAULT current_date NOT NULL,
> "student_id" text,
> "dep_id" text,
> "password" text NOT NULL,
> "validated" bool DEFAULT 'f' NOT NULL,
> PRIMARY KEY (firstname, lastname, adres, zipcode));
+--------------------------+--------------------------------------+
| Stuart C. G. Rison | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street |
| N.B. new phone code!! | London, W1P 8BT |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM |
| Fax. +44 (0)207 878 4040 | stuart(at)ludwig(dot)ucl(dot)ac(dot)uk |
+--------------------------+--------------------------------------+