From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Advice on index and constraint definition |
Date: | 2014-09-20 18:39:34 |
Message-ID: | 1411238374525-5819804.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
JORGE MALDONADO wrote
> Hi,
>
> I have a table like the one below where all of the fields are foreign keys
> except "prv_id" which is the primary key:
>
> prv_id serial
> prv_user_id inetger
> prv_client_id integer
> prv_customs_id integer
> prv_patent_id integer
> prv_doctype_id integer
>
>
> a) I read that it is a good idea to add an index for every foreign key in
> order to improve record management when selecting data for example.
>
> b) I need to avoid duplicates in the combination of fields "prv_user_id,
> prv_client_id, prv_customs_id, prv_patent_id, prv_doctype_id" so this
> drives me to set a constraint with such a field combination.
>
> Are statements (a) and (b) correct?
>
> Respectfully,
> Jorge Maldonado
Both pieces of advice are valid though I'd generally question a design that
uses 5 foreign keys on a matching table. Most matching tables match two
items. Now if, say, user-client is already foreign key then that pair is
only a single constraint and you wouldn't need individual constraints on
each field.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-on-index-and-constraint-definition-tp5819799p5819804.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | JORGE MALDONADO | 2014-09-20 18:55:39 | Re: Advice on index and constraint definition |
Previous Message | JORGE MALDONADO | 2014-09-20 17:51:42 | Advice on index and constraint definition |