I thought I had understood the question of primary key and foreign keys in the context of inheritance, but it seems I don't, and I would appreciate some light over this situation. Here's the setup:
I have a parent table entitled "generic", composed of a SERIAL primary key "id" plus a few other columns, one of which is a not-null character-type entitled "kind". The possible values of "kind" are constrained to be only 'a' or 'b'.
Table "generic" has two children: "child_a" and "child_b". In "child_a", the column "kind" is constrained to be 'a'; in "child_b", the value of "kind" is constrained to 'b'. The two children differ slightly in the structure of the columns that aren't part of their "generic" parent. Moreover, all the tuples of "generic" have to belong to either "child_a" or "child_b". (In other words, any tuple appearing in "generic" has been inserted in either "child_a" or "child_b".)
In parallel with all that, I have another table "family" containing an attribute which references (as foreign key) the "id" of table "generic". You see, "family" isn't really bothered to know whether it is referencing a tuple in "child_a" or one in "child_b": if we need to access the extra columns proper to the children, we'll do that at a later stage...
However, Postgres isn't too happy about that: when, in cousin, I insert a reference to a tuple of "generic", I am told:
ERROR: insert or update on table "cousin" violates foreign key constraint "cousin_generic_fkey" DETAILS: Key (family_relative)=(5) is not present in table "generic"
All the while, a SELECT on "generic" will give something like:
id | kind | reliability ----+------+------------- 4 | b | 2 5 | a | 8
In other words, although the tuple with id=5 appears in the SELECT query, it seems that Postgres would rather I referenced it inside "child_a" rather than inside "generic". All along, I had been under the impression that a tuple belonging to a child automatically belongs to the parent, yet I wouldn't be able to reference that tuple???
Is that normal behaviour? Can I relax the foreign key constraint so as to be able to reference any item within "generic", whether the tuple originally came from "generic" or from one of its children?
