Thread: Foreign Key on Inheriting Table?

There's some mention in the (old!) documentation that constraints such as foreign keys won't include data from
inheritingtables, eg: 

  CREATE TABLE foo (
     id SERIAL PRIMARY KEY
  );

  CREATE TABLE bar (
     attribute integer NOT NULL
  ) INHERITS (foo);

  CREATE TABLE bar_widgets (
     bar integer CONSTRAINT bar_exists REFERENCES foo (id)
  );


Now if you populate bar, you can't put anything in bar_widgets, because the foreign key constraint is not satisfied.

Similarly, if you want to have self-referencing items (eg: two points link together):

  CREATE TABLE anomalies (
     id integer PRIMARY KEY,
     x integer NOT NULL,
     y integer NOT NULL
  );

  CREATE TABLE wormholes (
     other_end integer CONSTRAINT has_end REFERENCES wormholes (id)
  ) INHERITS (anomalies);


This won't work because the wormholes tables doesn't actually have the id column (the anomalies table has it).

This won't work either:

  CREATE TABLE wormhole_tubes (
     left_end integer CONSTRAINT left_exists REFERENCES wormholes (id),
     right_end integer CONSTRAINT right_exists REFERENCES wormholes (id)
  );


While I could adjust my code to treat wormholes separately to all other anomalies, I was hoping to take advantage of
theobject-relational features of PostgreSQL to make my work a little easier. 

Does anyone know whether I'm just doing something wrong, or is the old documentation still correct?

Thanks
Alex Satrapa

On Mon, 9 Feb 2004, Alex Satrapa wrote:

> Does anyone know whether I'm just doing something wrong, or is the old documentation still correct?

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.

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

Alex,

[N.B. I just read the second message you sent on this question, but I have a
solution that goes in a different direction.]

I ran into the same problem after designing a system based on inheritance,
and asked a similar question a couple of weeks ago -- a couple of folks here
gave very helpful replies to my query.

I ended up ditching the table inheritance mechanism, because I really need
foreign keys to work on all records in base and inheriting tables. So
instead I linked tables with foreign keys on the ids, then defined views
with rules on insert/update/delete to make it all work like inheritance. I
actually like the result better, but it's more work to set it up. Like so:

create table objects (
    id serial primary key,
    name varchar
) without oids;

create table documents (
    id integer primary key references objects (id) on delete cascade,
    body text
) without oids;

create or replace view documents_objects as
    select objects.*, body from objects, documents
    where objects.id = documents.id;

create table articles (
    id integer primary key references documents (id) on delete cascade,
    title varchar
) without oids;

create or replace view articles_objects (
    select documents_objects.*, title from documents_objects, articles
    where documents_objects.id = articles.id;

<etc>
<add rules, functions, and triggers; stir until thickened.>

FWIW,
Shawn Harrison

----- Original Message -----
From: "Alex Satrapa" <alex@lintelsys.com.au>
To: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: Sunday, February 08, 2004 4:10 PM
Subject: [GENERAL] Foreign Key on Inheriting Table?


> There's some mention in the (old!) documentation that constraints such as
foreign keys won't include data from inheriting tables, eg:
>
>   CREATE TABLE foo (
>      id SERIAL PRIMARY KEY
>   );
>
>   CREATE TABLE bar (
>      attribute integer NOT NULL
>   ) INHERITS (foo);
>
>   CREATE TABLE bar_widgets (
>      bar integer CONSTRAINT bar_exists REFERENCES foo (id)
>   );
>
>
> Now if you populate bar, you can't put anything in bar_widgets, because
the foreign key constraint is not satisfied.
>
> Similarly, if you want to have self-referencing items (eg: two points link
together):
>
>   CREATE TABLE anomalies (
>      id integer PRIMARY KEY,
>      x integer NOT NULL,
>      y integer NOT NULL
>   );
>
>   CREATE TABLE wormholes (
>      other_end integer CONSTRAINT has_end REFERENCES wormholes (id)
>   ) INHERITS (anomalies);
>
>
> This won't work because the wormholes tables doesn't actually have the id
column (the anomalies table has it).
>
> This won't work either:
>
>   CREATE TABLE wormhole_tubes (
>      left_end integer CONSTRAINT left_exists REFERENCES wormholes (id),
>      right_end integer CONSTRAINT right_exists REFERENCES wormholes (id)
>   );
>
>
> While I could adjust my code to treat wormholes separately to all other
anomalies, I was hoping to take advantage of the object-relational features
of PostgreSQL to make my work a little easier.
>
> Does anyone know whether I'm just doing something wrong, or is the old
documentation still correct?
>
> Thanks
> Alex Satrapa
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend