467,132 Members | 1,139 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,132 developers. It's quick & easy.

Confusion about inheritance

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 / fe***@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
  • viewed: 915
Share:
1 Reply
On Wed, 12 May 2004 fe***************@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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by JKop | last post: by
5 posts views Thread by ma740988@pegasus.cc.ucf.edu | last post: by
22 posts views Thread by Matthew Louden | last post: by
9 posts views Thread by vidalsasoon | last post: by
6 posts views Thread by Ranginald | last post: by
6 posts views Thread by Hook | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.