[SQL] 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
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 ([email protected]) 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL]
Ok, got it. Thank you every one for your response. Thanks and Regards Kaleeswaran Velu On Tuesday, October 8, 2013 10:00 PM, Andreas Kretschmer wrote: Kaleeswaran Velu wrote: > Hi Team, > I am using PostgreSQL 9.2.3 in Windows platform. I have created two databases > in it. Now I want to refer the tables across the databases. Meaning would like > to create Database link. Can anyone guide me on how to create a DB link? You can use db_link, see http://www.postgresql.org/docs/9.2/interactive/dblink.html. 9.3 contains more: http://www.postgresql.org/docs/9.3/interactive/postgres-fdw.html But in general: don't use different databases if you need a cross-database-connection. You can use Schemas instead: http://www.postgresql.org/docs/9.2/static/ddl-schemas.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
