[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] Advice on defining indexes
From:       David Johnston <polobo () yahoo ! com>
Date:       2013-10-04 22:44:33
Message-ID: 1380926673606-5773428.post () n5 ! nabble ! com
[Download RAW message or body]

JORGE MALDONADO wrote
> If a table has a foreign key on 2 fields, should I also create an index
> composed of such fields?

Yes.

If you want to truly/actually model a foreign key the system will require
you to create a unique constraint/index on the "primary/one" side of the
relationship.

CREATE TABLE list ( lst_source, lst_date, FOREIGN KEY (lst_source, lst_date)
REFERENCES source (src_id, src_date) ...;

If a unique constraint (in this case I'd suggest primary key) does not exist
for source(src_id, src_date) the create table with the foreign key will
fail.

David J.





--
View this message in context: \
http://postgresql.1045698.n5.nabble.com/Advice-on-defining-indexes-tp5773423p5773428.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


[prev in list] [next in list] [prev in thread] [next in thread]