5

Note: this is not the same as this SO question which is about adding redundant information in the tables themselves.

Does the final statement in the below series of statements buy me anything in terms of indexing / query optimization or is it totally useless and an anti-pattern? From a referential integrity viewpoint it is useless but might there be some benefit in indexing, or query processing speed for instance, e.g. in a JOIN only between the "child" and "grandfather" tables?

CREATE TABLE grandfather (i INTEGER);
ALTER TABLE  grandfather ADD PRIMARY KEY (i);

CREATE TABLE father  (i, j INTEGER);
ALTER TABLE  father ADD PRIMARY KEY (i, j);
ALTER TABLE  father ADD CONSTRAINT father_2_grandfather FOREIGN KEY (i) REFERENCES grandfather(i);

CREATE TABLE child (i, j, k INTEGER);
ALTER TABLE  child ADD PRIMARY KEY (i, j, k);
ALTER TABLE  child ADD CONSTRAINT child_2_father      FOREIGN KEY (i, j) REFERENCES father(i, j);
-- is the below statement totally useless ?
ALTER TABLE  child ADD CONSTRAINT child_2_grandfather FOREIGN KEY (i)    REFERENCES grandfather(i);
1
  • 3
    It's not useful at all I think. The only exception might be to help brain-dead ORMs by explicitly telling them that there is an (implicit but trusted) foreign key relationship from child to grandfather through the father an the other 2 FKs. Feb 15 '14 at 0:44
1

(For similar relations simple hierarchy are more sensible... (id, parent))

Apart from that the last foreign key only slows down a bit the DML queries.

0

Long answer:

https://stackoverflow.com/a/970605/1676382

PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships.

...

Therefore you have to create indexes on foreign-keys yourself if you want them.

http://mlawire.blogspot.ca/2009/08/postgresql-indexes-on-foreign-keys.html

(Postgresql does not create indexes on foreign keys automatically).

Short answer: FKs do not provide optimization when querying--you'll need a proper index for that.

1
  • 1
    Please include the main points from those posts. They might get deleted, moved or whatever, rendering your answer into something that lacks explanation.
    – dezso
    Feb 3 '17 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.