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/
Show quoted text
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