Re: [SQL] Question about index/constraint definition in a table
JORGE MALDONADO wrote I have a table as follows: Table Artist Colaborations * car_id (integer field, primary key) * car_song (integer field, foreign key, foreign table is a catalog of songs) * car_artist (integer field, foreign key, foreign table is a catalog of artists) So, I added 2 indexes to improve JOIN in queries: 1. An index for car_song which accepts duplicates. 2. An index for car_artist which accepts duplicates. Now, the combination of car_song + car_artist cannot be duplicated so I think that adding a constraint on these 2 fields is the solution. My question: Is this the correct way to go? Respectfully, Jorge Maldonado Yes. Why is it this is a question for you? Also, the car_id field becomes pointless since your new constraint is the true and natural PK. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Question-about-index-constraint-definition-in-a-table-tp5773924p5773925.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question about index/constraint definition in a table
David Johnston wrote JORGE MALDONADO wrote I have a table as follows: Table Artist Colaborations * car_id (integer field, primary key) * car_song (integer field, foreign key, foreign table is a catalog of songs) * car_artist (integer field, foreign key, foreign table is a catalog of artists) So, I added 2 indexes to improve JOIN in queries: 1. An index for car_song which accepts duplicates. 2. An index for car_artist which accepts duplicates. Now, the combination of car_song + car_artist cannot be duplicated so I think that adding a constraint on these 2 fields is the solution. My question: Is this the correct way to go? Respectfully, Jorge Maldonado Yes. Why is it this is a question for you? Also, the car_id field becomes pointless since your new constraint is the true and natural PK. David J. with index only scans it seems that defining a pair of unique indexes (and no single column indexes) would have value. How much value I do not know. Would still want to drop the artificial id field. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Question-about-index-constraint-definition-in-a-table-tp5773924p5773942.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
