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);