Re: Foreign Key on Inheriting Table?

Stephan Szabo wrote:
> This still works the same way it has.
>
> If you look through the archives, you should hopefully be able to scrounge
> up some of the workaround information that's been sent in the past.

For reference, if anyone finds this thread through Google or whatever, here is the result of some quick Googling on my part. As an aside, if you want to find answer instead of questions in your Google search, add the phrase "Stephan Szabo" - I get the impression that Stephan has the "use a different table for the unique index for the hierarchy" answer in a .sig by now ;)

Workaround 1: Use a separate table for the field (eg: "id") that is going to be used as the foreign key, and have all tables in the hierarchy reference that table:
* http://archives.postgresql.org/pgsql-general/2002-06/msg01036.php
* http://archives.postgresql.org/pgsql-novice/2004-01/msg00149.php

Workaround 2: Create unique indexes on each of the child tables in the hierarchy to allow that column on that child table to be used as a foreign key:
* http://archives.postgresql.org/pgsql-bugs/2000-12/msg00131.php

From what I've read, the problem arises because the (unique) index doesn't descend an inheritance hierarchy. This causes two problems - the first being that child tables won't have the "unique" index on their inherited columns. The second problem is that the uniqueness check doesn't apply to data inserted into any tables other than the original parent. I guess that's just two different sides of the same coin - the index doesn't span the hierarchy, therefore the uniqueness doesn't span the hierarchy either. Any uniqueness is specific to a child table *iff* a unique index is specified on that child table.

I am not a PostgreSQL programmer, and I doubt I'll get any time to look at the code in the depth required to propose a solution to this problem, so I'll settle for re-documenting Stephan's suggestion. How much would it break existing code to have PostgreSQL issue a warning or notice when someone attempts to CREATE (UNIQUE) INDEX on a column that is inherited? At least the programmer would then be alerted to the problem before it bites them.

For the record, this "foreign key not working on tables defined using inherit" issue affects all versions of PostgreSQL up to at least 7.4.1.

HTH
Alex Satrapa