pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stuart Rison <stuart@ludwig.ucl.ac.uk>
To: Frederic.De.Leersnijder@pandora.be
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] database design SQL prob.
Date: Fri, 23 Jul 1999 10:12:52 +0100
Message-ID: <v04020a00b3bddd224bc2@[128.40.242.190]> (raw)

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@ludwig.ucl.ac.uk              |
+--------------------------+--------------------------------------+



view thread (7+ messages)  latest in thread

Message-ID: <v04020a00b3bddd224bc2@[128.40.242.190]>
Permalink:  ../v04020a00b3bddd224bc2@%5B128.40.242.190%5D/
Also on:    postgresql.org/message-id/v04020a00b3bddd224bc2@[128.40.242.190]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: stuart@ludwig.ucl.ac.uk, Frederic.De.Leersnijder@pandora.be
  Subject: Re: [SQL] database design SQL prob.
  In-Reply-To: <v04020a00b3bddd224bc2@[128.40.242.190]>
  References: <v04020a00b3bddd224bc2@[128.40.242.190]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox