pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: 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: Sun, 25 Jul 1999 15:40:48 +0300
Message-ID: <l03130301b3c0b5c2705b@[147.233.159.109]> (raw)
In-Reply-To: <v04020a00b3bddd224bc2@[128.40.242.190]>

At 12:12 +0300 on 23/07/1999, Stuart Rison wrote:


> 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).

To be more exact, primary keys are defined as NOT NULL implicitly. If you
want to emulate primary keys, you have to define the key as NOT NULL as
well as define a unique index on it.

But for the original question: define the primary key on the id field, and
a unique index on the combination of fields that you want to be unique. Now
everything makes sense. The field used for reference, which is by
definition the primary key of the table, is indeed defined as primary key.
The combination of fields which is not used for references, but which needs
to be unique, is defined as unique.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (7+ messages)  latest in thread

Message-ID: <l03130301b3c0b5c2705b@[147.233.159.109]>
Permalink:  ../l03130301b3c0b5c2705b@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130301b3c0b5c2705b@[147.233.159.109]

 · 

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: herouth@oumail.openu.ac.il, stuart@ludwig.ucl.ac.uk, Frederic.De.Leersnijder@pandora.be
  Subject: Re: [SQL] database design SQL prob.
  In-Reply-To: <l03130301b3c0b5c2705b@[147.233.159.109]>
  References: <v04020a00b3bddd224bc2@[128.40.242.190]> <l03130301b3c0b5c2705b@[147.233.159.109]>

* 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