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