Foreign keys and inheritance

From: "Pierre Thibaudeau" <pierdeux(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Foreign keys and inheritance
Date: 2006-04-07 02:34:55
Message-ID: 74b035bb0604061934i3315510ev26b12da32d6a2c0b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

(Running PostgreSQL v.8.1.3 on WindowsXP.)

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Pierre Thibaudeau 2006-04-07 04:18:33 Re: Layout question
Previous Message operationsengineer1 2006-04-06 23:01:59 Re: On "linking" the types of two columns