Thread: Inheritance and reference integrity

Hi!

I wonder why following SQL-sequence will fail:

-- DROP TABLE car;
-- DROP TABLE vehicle;
-- DROP SEQUENCE vehicle_id_seq;

CREATE TABLE vehicle(
        id      SERIAL,
        color   TEXT,
--
        CONSTRAINT PK_vehicle PRIMARY KEY (id)
);


CREATE TABLE car(
        driver  TEXT
)INHERITS(vehicle);


-- DROP TABLE foobar;

CREATE TABLE foobar(
        snafu   TEXT,
        driver_id  INTEGER CONSTRAINT CR_foobar_driver_id REFERENCES car (id)
);

Result:

psql:inherits_test.sql:25: NOTICE:  CREATE TABLE will create implicit
trigger(s) for FOREIGN KEY check(s)
psql:inherits_test.sql:25: ERROR:  UNIQUE constraint matching given keys
for referenced table "car" not found


Is it just me or postgres? =)

BR, Jani

P.S. My Postgresql's version number is 7.1.

---
Jani Averbach

Jani Averbach writes:

> CREATE TABLE vehicle(
>         id      SERIAL,
>         color   TEXT,
>         CONSTRAINT PK_vehicle PRIMARY KEY (id)
> );
> CREATE TABLE car(
>         driver  TEXT
> )INHERITS(vehicle);
> CREATE TABLE foobar(
>         snafu   TEXT,
>         driver_id  INTEGER CONSTRAINT CR_foobar_driver_id REFERENCES car (id)
> );

> psql:inherits_test.sql:25: ERROR:  UNIQUE constraint matching given keys
> for referenced table "car" not found

The primary key is not inherited by "car" from "vehicle".  In general,
mixing foreign keys and inheritance is not recommendable in the current
state of the implementation.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter