Обсуждение: Is a primary key made of a couple columns so much better than a unique() constraint ?

Hi everybody,

i'd like to know if it is better to use a primary key made of a couple
columns, than to use a constraint UNIQUE() on this couple columns,
regarding the sake of postgresql.

For example, i've got these two tables, 'buyer' and 'purchaser' and i
want to make an association table buyer_purchaser in which i get only
some couples (id_buyer, id_purchaser), with a unique() contraint on them.

If i put a primary key on the couple, i won't use it, because the
framework on top of it is unable to use a primary key made of a couple of
columns.
Si my question is : is it better, in so far as only the database is
concerned, to have a primary key than a unique() contraint on a couple
columns ?

Thanks in advance for your answers,
David

--
David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source

On Mon, Oct 10, 2005 at 04:44:54PM +0200, David Pradier wrote:
> Hi everybody,
>
> i'd like to know if it is better to use a primary key made of a couple
> columns, than to use a constraint UNIQUE() on this couple columns,
> regarding the sake of postgresql.

In PostgreSQL, both primary keys and UNIQUE constraints are implemented
via UNIQUE indexes, ergo there is no difference...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

> In PostgreSQL, both primary keys and UNIQUE constraints are implemented
> via UNIQUE indexes, ergo there is no difference...

Thanks Martijn, it's exactly what i wanted to know.

Best regards,
David

--
David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Mon, Oct 10, 2005 at 04:44:54PM +0200, David Pradier wrote:
>> i'd like to know if it is better to use a primary key made of a couple
>> columns, than to use a constraint UNIQUE() on this couple columns,
>> regarding the sake of postgresql.

> In PostgreSQL, both primary keys and UNIQUE constraints are implemented
> via UNIQUE indexes, ergo there is no difference...

Just for the sake of completeness, there are exactly two differences:

* PRIMARY KEY implies NOT NULL on the key columns; UNIQUE doesn't.

* PRIMARY KEY creates a default target for foreign key references,
  ie, if you've declared a primary key then you can later just say
  "REFERENCES mytab" instead of spelling out "REFERENCES mytab(keycol)".

So "UNIQUE + NOT NULL" is pretty dang close to the same as "PRIMARY
KEY", but not quite.

            regards, tom lane

> Just for the sake of completeness, there are exactly two differences:
> * PRIMARY KEY implies NOT NULL on the key columns; UNIQUE doesn't.
> * PRIMARY KEY creates a default target for foreign key references,
>   ie, if you've declared a primary key then you can later just say
>   "REFERENCES mytab" instead of spelling out "REFERENCES mytab(keycol)".
> So "UNIQUE + NOT NULL" is pretty dang close to the same as "PRIMARY
> KEY", but not quite.
>             regards, tom lane

Thanks too, Tom :-)

Best regards,
David

--
David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source