[BUGS] BUG #1689: problem with inheritance and foreign keys

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[BUGS] BUG #1689: problem with inheritance and foreign keys

wrobell

The following bug has been logged online:

Bug reference:      1689
Logged by:          
Email address:      [hidden email]
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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] BUG #1689: problem with inheritance and foreign keys

Stephan Szabo
On Sun, 29 May 2005,  wrote:

>
> The following bug has been logged online:
>
> Bug reference:      1689
> Logged by:
> Email address:      [hidden email]
> 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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match