Re: Advice on index and constraint definition

Thanks for your answer. I am a DB novice and I still have questions, I
would like you to explain a bit more about the following:

" I'd generally question a design that uses 5 foreign keys on a matching
table"

Does this mean that my table with 5 foreign keys should have fewer foreign
keys? How could I achieve this goal if such a table depends on 5 other
tables? Maybe a must take into account a redesign of this precise table.

Regards.

On Sat, Sep 20, 2014 at 1:39 PM, David G Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>