PostgreSQL Bugs - BUG #1689: problem with inheritance and foreign keys

Author

BUG #1689: problem with inheritance and foreign keys

2005-05-31, 11:24 am

The following bug has been logged online:

Bug reference: 1689
Logged by:
Email address: wrobell@pld-linux.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.

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

http://archives.postgresql.org

Stephan Szabo

2005-05-31, 11:24 am

On Sun, 29 May 2005, wrote:

>
> The following bug has been logged online:
>
> Bug reference: 1689
> Logged by:
> Email address: wrobell@pld-linux.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.

---------------------------(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

Sponsored Links