Confusion about inheritance

Started by Nonamealmost 22 years ago2 messagesgeneral
Jump to latest
#1Noname
felix-lists-pgsql@crowfix.com

I converted some tables from individual definitions to use object
inheritance and now get failures of a foreign key constraint.

Here is the file which creates schema tst and the tables:

++++++++++++++++ begin
DROP SCHEMA tst CASCADE;
CREATE SCHEMA tst;
set search_path = tst;

CREATE TABLE a (
a SERIAL PRIMARY KEY
);

CREATE TABLE aa (
payload TEXT
) INHERITS (a);

CREATE TABLE b (
a INT REFERENCES a PRIMARY KEY
);

CREATE FUNCTION new_aa(TEXT) RETURNS INT AS '
DECLARE
a_id INT;
dummy INT;
BEGIN
INSERT INTO aa (payload) VALUES ($1);
SELECT INTO a_id currval(\'a_a_seq\');
SELECT INTO dummy a FROM b WHERE a = a_id;
IF NOT FOUND THEN
INSERT INTO b (a) VALUES (a_id);
END IF;
RETURN a_id;
END
' LANGUAGE plpgsql;

CREATE TABLE xx (
x SERIAL PRIMARY KEY,
payload TEXT
);

CREATE TABLE y (
x INT REFERENCES xx PRIMARY KEY
);

CREATE FUNCTION new_xx(TEXT) RETURNS INT AS '
DECLARE
x_id INT;
dummy INT;
BEGIN
INSERT INTO xx (payload) VALUES ($1);
SELECT INTO x_id currval(\'xx_x_seq\');
SELECT INTO dummy x FROM y WHERE x = x_id;
IF NOT FOUND THEN
INSERT INTO y (x) VALUES (x_id);
END IF;
RETURN x_id;
END
' LANGUAGE plpgsql;
++++++++++++++++ end

Table aa inherits its primary key from table a, and table b references
a's primary key.

Tables xx and y follow aa and b without the inheritance.

When I try "SELECT new_xx('howdy')", no pronlem:

new_xx
--------
1
(1 row)

When I try "SELECT new_aa('doody')", I get this error:

ERROR: insert or update on table "b" violates foreign key constraint "$1"
DETAIL: Key (a)=(1) is not present in table "a".

This is 7.4.2.

I am by no means an expert on SQL or PostgreSQL. I have been poking
at it, learning outer joins, triggers, rules, etc, and if I am doing
something incredibly stupid here, I'd appreciate knowing. Searching
the tech docs for inheritance did not find anything useful.

--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

#2Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Noname (#1)
Re: Confusion about inheritance

On Wed, 12 May 2004 felix-lists-pgsql@crowfix.com wrote:

Table aa inherits its primary key from table a, and table b references
a's primary key.

Currently, tables do not inherit primary keys, unique constraints or
references constraints. This is a known deficiency with inheritance
currently.