Thread: Advice on index and constraint definition
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
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.
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.g.johnston@gmail.com> wrote:
JORGE MALDONADO wroteBoth pieces of advice are valid though I'd generally question a design that> 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
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
JORGE MALDONADO wrote > 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. My point is a single table relating five other individual tables is quite probably not properly normalized. If you are using multi-column primary keys then the presence of 5 columns might only represent 2 source tables which would be more 'normal'. This kind of table typically is used for a direct many-to-many relationship so having 5 foreign keys would definitely complicate the logic. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-on-index-and-constraint-definition-tp5819799p5819815.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.