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
.sigby 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
tablesin 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
tableto 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
causestwo problems - the first being that child tables won't have the "unique" index on their inherited columns. The
secondproblem 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
uniquenessdoesn't span the hierarchy either. Any uniqueness is specific to a child table *iff* a unique index is
specifiedon 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
solutionto this problem, so I'll settle for re-documenting Stephan's suggestion. How much would it break existing code
tohave 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
upto at least 7.4.1.
HTH
Alex Satrapa
