Thread: Question about index/constraint definition in a table
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
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.
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.