Skip site navigation (1) Skip section navigation (2)

BUG #1636: Foreign key referencing inherited table fails.

From: "Fredrik Olsson" <peylow(at)atari(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1636: Foreign key referencing inherited table fails.
Date: 2005-04-28 15:31:45
Message-ID: 20050428153145.9424EF1381@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1636
Logged by:          Fredrik Olsson
Email address:      peylow(at)atari(dot)org
PostgreSQL version: 8.0.2
Operating system:   Max OS X 10.3.9
Description:        Foreign key referencing inherited table fails.
Details: 

Some SQL statements says more then 1000 words :).

CREATE TABLE employees (
  nr serial PRIMARY KEY,
  name varchar(32)
);

CREATE TABLE managers (
  loves_meetings boolean DEFAULT TRUE NOT NULL
) INHERITS (employees);

CREATE TABLE events (
  summoner integer NOT NULL REFERENCES employees (nr),
  at timestamp DEFAULT now() NOT NULL,
  reason text NOT NULL,
  PRIMARY KEY (summoner, at)
);

INSERT INTO employees (name) VALUES ('Bob');
INSERT INTO managers (name) VALUES ('Alice');

-- This makes an ugly assumption of nr starting at 1 and Alice having nr=2.

INSERT INTO events (summoner, reason) VALUES (2, 'Brag about
inheritance.');

The last statement will fail with this error message:
ERROR:  insert or update on table "events" violates foreign key constraint
"events_summoner_fkey"
DETAIL:  Key (summoner)=(2) is not present in table "employees".

The foreign key from events (summoner) referencing employees (nr) can be
removed and replaced by triggers with ease. But I still think it is a bug,
and that foreign key references to tables that are inherited from should be
legal, even if referenced rows orginates from child tables.

Responses

pgsql-bugs by date

Next:From: Ingolf KnopfDate: 2005-04-28 15:48:32
Subject: Patch for Bug#1611
Previous:From: Bruno Wolff IIIDate: 2005-04-28 01:32:28
Subject: Re: BUG #1633: about transactions and row level locking

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group