Date: Fri, 4 Oct 2013 15:44:33 -0700 (PDT)
From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Message-ID: <1380926673606-5773428.post@n5.nabble.com>
In-Reply-To: 
 <CAAY=A7_rVJzSCEjqhkjVMfbhYCHueRF2oXzrCXsjMDTVy3sX1w@mail.gmail.com>
References: 
 <CAAY=A7-0zDKj6diOa7i-jX+biriJ9z-XFjjw-wM6jLzSKQ3Y9A@mail.gmail.com>
 <1380924543966-5773424.post@n5.nabble.com>
 <CAAY=A7_rVJzSCEjqhkjVMfbhYCHueRF2oXzrCXsjMDTVy3sX1w@mail.gmail.com>
Subject: Re: Advice on defining indexes
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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