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.
