Re: BUG #1689: problem with inheritance and foreign keys

On Sun, 29 May 2005, wrote:

>
> The following bug has been logged online:
>
> Bug reference: 1689
> Logged by:
> Email address: wrobell(at)pld-linux(dot)org
> PostgreSQL version: 8.0.3
> Operating system: Linux
> Description: problem with inheritance and foreign keys
> Details:
>
> ----- schema script -------
>
> create table employee (
> __key__ integer,
> name varchar(10) not null,
> surname varchar(20) not null,
> phone varchar(12) not null,
> unique (name, surname),
> primary key (__key__)
> );
>
> create table boss (
> dep_fkey integer unique
> ) inherits(employee);
>
>
> create table department (
> __key__ integer,
> employee_fkey integer unique,
> primary key (__key__),
> foreign key (employee_fkey) references employee(__key__) initially
> deferred
> );
>
> alter table boss add foreign key (dep_fkey) references department(__key__)
> initially deferred;
>
>
> ---------------------------
>
> ----- data script ----------
> begin;
>
> \qecho - - - adding boss - - -
>
> insert into boss (__key__, name, surname, phone, dep_fkey)
> values (2, 'a', 'b', 'p', 1);
>
> select * from employee;
>
> \qecho - - - adding department - - -
>
> insert into department (__key__, employee_fkey)
> values (1, 2);
>
> commit;
>
>
> ---------------------------
>
> Please:
> - create database
> - run "schema" script
> - run "data" script
>
> The output of "data" script:
> --------------------------------
> BEGIN
> - - - adding boss - - -
> INSERT 32564 1
> __key__ | name | surname | phone
> ---------+------+---------+-------
> 2 | a | b | p
> (1 row)
>
> - - - adding department - - -
> INSERT 32565 1
> psql:g.sql:15: ERROR: insert or update on table "department" violates
> foreign key constraint "department_employee_fkey_fkey"
> DETAIL: Key (employee_fkey)=(2) is not present in table "employee".
> --------------------------------
>
> As you can see, there is employee row with __key__ == 2. PostgreSQL states
> that it is not.

As with unique constraints, foreign keys refer only to the explicitly
named table and not any subtables. There is a row in the full hierarchy
represented by employee but not in employee itself. There have been
hackarounds discussed in the past which you can find in the archives.

Inheritance really needs alot of work to be generally useful and needs a
few champions.