Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA06391 for ; Sun, 25 Jul 1999 08:40:16 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id C1FFF4B57A; Sun, 25 Jul 1999 15:40:14 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA1631; Sun, 25 Jul 1999 15:43:32 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Sun, 25 Jul 1999 15:40:48 +0300 To: Stuart Rison , Frederic.De.Leersnijder@pandora.be, pgsql-sql@postgresql.org From: Herouth Maoz Subject: Re: [SQL] database design SQL prob. 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