[GENERAL] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is a primary key made of a couple columns so much better than a unique() constraint ?
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 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. pgpUZC5DxlAs4.pgp Description: PGP signature
Re: [GENERAL] Is a primary key made of a couple columns so much better than a unique() constraint ?
> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is a primary key made of a couple columns so much better than a unique() constraint ?
Martijn van Oosterhout 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Is a primary key made of a couple columns so much better than a unique() constraint ?
> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
