Re: Advice on index and constraint definition - Mailing list 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.


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice