Thread: integrity and inherit
Hi ...
I hope someone will be so kind to take the time to explain to me what I
have been doing wrong in this example.
I have used PosgreSQL now for a while, and I really like to begin to use
some more of its integrity checks. The database I have been creating
uses the inherit functionality of PG, and this works out beautiful :-)
But I can't make the combination work ! To point out the problem i have
made this small example :
CREATE TABLE base (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE derived (
value INTEGER
) INHERITS( base );
CREATE TABLE test_ref (
ref_id INTEGER REFERENCES base,
name TEXT
);
This is the setup, and all I get is a few "NOTICE", about indirect check
and indexes, really nice. Time to use it :-)
INSERT INTO base (id, name) VALUES( 1, 'mother' );
INSERT INTO derived (id, name, value) VALUES( 2, 'child', 42 );
INSERT INTO test_ref (ref_id, name) VALUES( 1, 'mother' );
All this works, but this one dont :
INSERT INTO test_ref (ref_id, name) VALUES( 2, 'child' );
Here it says :
ERROR: <unnamed> referential integrity violation - key referenced from
test_ref not found in base
But a quick :
SELECT * FROM base;
shows :
id | name
----+--------
1 | mother
2 | child
So as fare as I can see, this isn't correct, the id value number 2 is in
"base" table.
Could someone please tell me what I have done wrong ?
/BL
Bo Lorentsen wrote: >I hope someone will be so kind to take the time to explain to me what I >have been doing wrong in this example. ... >CREATE TABLE base ( > id INTEGER PRIMARY KEY, > name TEXT >); > >CREATE TABLE derived ( > value INTEGER >) INHERITS( base ); > >CREATE TABLE test_ref ( > ref_id INTEGER REFERENCES base, > name TEXT >); ... >INSERT INTO base (id, name) VALUES( 1, 'mother' ); >INSERT INTO derived (id, name, value) VALUES( 2, 'child', 42 ); > >INSERT INTO test_ref (ref_id, name) VALUES( 1, 'mother' ); > >All this works, but this one dont : > >INSERT INTO test_ref (ref_id, name) VALUES( 2, 'child' ); > >Here it says : > >ERROR: <unnamed> referential integrity violation - key referenced from >test_ref not found in base Referential integrity doesn't support references to inheritance hierarchies. You will have to redesign your database. For example, have a trigger on each table in the hierarchy to update a table of keys and use RI on that; it would also help you to maintain primary keys -- you probably think that table derived inherits the primary key constraint from base, but it doesn't. That is another problem with the current implementation of inheritance. :-( -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "For God hath not appointed us to wrath, but to obtain salvation by our Lord Jesus Christ, Who died for us, that, whether we wake or sleep, we should live together with him." I Thessalonians 5:9,10
Oliver Elphick wrote: Thanks for the quick answer ! > Referential integrity doesn't support references to inheritance hierarchies. > You will have to redesign your database. That is sad news, is this scheduled to be fixed in the near future ? > For example, have a trigger on each table in the hierarchy to update a > table of keys and use RI on that; it would also help you to maintain > primary keys -- you probably think that table derived inherits the > primary key constraint from base, but it doesn't. That is another problem > with the current implementation of inheritance. :-( I'm not quite sure what you meen. Does this mean that I sould make a rule on insert on every enherited table, that makes sure the base is updated ? When I do my "SELECT * FROM base" and see the value id the ID, what kind of value am I then looking at ? /BL
Bo Lorentsen wrote:
>Oliver Elphick wrote:
>> Referential integrity doesn't support references to inheritance hierarchie
>s.
>> You will have to redesign your database.
>
>That is sad news, is this scheduled to be fixed in the near future ?
It's in the TODO list...
>> For example, have a trigger on each table in the hierarchy to update a
>> table of keys and use RI on that; it would also help you to maintain
>> primary keys -- you probably think that table derived inherits the
>> primary key constraint from base, but it doesn't. That is another problem
>> with the current implementation of inheritance. :-(
>
>I'm not quite sure what you meen. Does this mean that I sould make a rule on
>insert on every enherited table, that makes sure the base is updated ?
There's an example below. resources is the ancestor and has four
descendants, all at the same level. A resource key must occur only
once in the whole hierarchy and needs to be available for RI. resource
is a deferred class; that is, every resource must be a member of one of
the four descendant classes, so resource itself should have no rows.
>When I do my "SELECT * FROM base" and see the value id the ID, what kind of
>value am I then looking at ?
You are seeing all the records from the hierarchy of base and all its
descendants; that part of inheritance works; but RI does not work (yet).
Example (see above):
-- Index of resources
CREATE TABLE resource_list (
id VARCHAR(4) PRIMARY KEY,
flag CHAR(1) NOT NULL
CONSTRAINT "valid flag"
CHECK (flag in ('B', 'M', 'L', 'T')),
name TEXT NOT NULL UNIQUE
)
;
CREATE TABLE resource
(
id VARCHAR(4),
...
,
PRIMARY KEY (id),
CONSTRAINT "resource in resource_list"
FOREIGN KEY (id) REFERENCES resource_list (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE
);
CREATE FUNCTION update_resource_list()
RETURNS opaque
AS '
-- update resource_list when a resource is added or deleted
DECLARE
flag CHAR(1);
cnt INTEGER := 0;
listrec RECORD;
BEGIN
IF TG_OP = ''DELETE'' THEN
DELETE FROM resource_list WHERE resource = OLD.id;
ELSE
IF TG_RELNAME = ''tool'' THEN
flag := ''T'';
ELSIF TG_RELNAME = ''bench'' THEN
flag := ''B'';
ELSIF TG_RELNAME = ''machine'' THEN
flag := ''M'';
ELSIF TG_RELNAME = ''labour'' THEN
flag := ''L'';
ELSE
RAISE ERROR ''resource_def() function called on unexpected table
%'', TG_RELNAME;
END IF;
FOR listrec IN EXECUTE ''SELECT * FROM resource_list WHERE id = ''
|| quote_literal(OLD.id) LOOP
cnt := cnt + 1
IF flag != listrec.flag OR id != OLD.id THEN
EXECUTE ''UPDATE resource_list SET id = '' ||
quote_literal(NEW.id) || '', flag = '' || quote_literal(flag) || '' WHERE id =
'' || quote_literal (OLD.id);
END IF;
END LOOP;
IF cnt = 0 THEN
EXECUTE ''INSERT INTO resource_list VALUES ('' ||
quote_literal(NEW.id) || '', '' ||
quote_literal(flag);
END IF;
END IF;
END;
' LANGUAGE 'plpgsql';
-- resource is a deferred class; it cannot have any rows itself
CREATE RULE no_resource AS ON INSERT
TO resource
DO INSTEAD NOTHING;
CREATE TABLE labour
(
pay NUMERIC(12,2) NOT NULL
CHECK (pay >= 0),
...
,
PRIMARY KEY (id),
CONSTRAINT "resource in resource_list"
FOREIGN KEY (id) REFERENCES resource_list (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE
)
inherits (resource)
;
CREATE TRIGGER on_labour_change BEFORE INSERT OR UPDATE
ON labour FOR EACH ROW
EXECUTE PROCEDURE update_resource_list();
CREATE TABLE bench
(
descr TEXT,
...
,
PRIMARY KEY (id),
CONSTRAINT "resource in resource_list"
FOREIGN KEY (id) REFERENCES resource_list (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE
)
INHERITS (resource)
;
CREATE TRIGGER on_bench_change BEFORE INSERT OR UPDATE
ON bench FOR EACH ROW
EXECUTE PROCEDURE update_resource_list();
CREATE TABLE machine
(
descrip TEXT NOT NULL,
...
,
PRIMARY KEY (id),
CONSTRAINT "resource in resource_list"
FOREIGN KEY (id) REFERENCES resource_list (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE
)
INHERITS (resource)
;
CREATE TRIGGER on_machineT_change BEFORE INSERT OR UPDATE
ON machine FOR EACH ROW
EXECUTE PROCEDURE update_resource_list();
CREATE TABLE tool
(
maker TEXT NOT NULL,
...
,
PRIMARY KEY (id),
CONSTRAINT "resource in resource_list"
FOREIGN KEY (id) REFERENCES resource_list (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE
)
INHERITS (resource)
;
CREATE TRIGGER on_tool_change BEFORE INSERT OR UPDATE
ON tool FOR EACH ROW
EXECUTE PROCEDURE update_resource_list();
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For God hath not appointed us to wrath, but to obtain
salvation by our Lord Jesus Christ, Who died for us,
that, whether we wake or sleep, we should live
together with him."
I Thessalonians 5:9,10
Oliver Elphick wrote: > It's in the TODO list... Are there any plans for whenever this will be fixed ? As fare as I can se, this is an important feature if you need to use PostgreSQL as a real OO DB. > There's an example below. resources is the ancestor and has four > descendants, all at the same level. A resource key must occur only > once in the whole hierarchy and needs to be available for RI. resource > is a deferred class; that is, every resource must be a member of one of > the four descendant classes, so resource itself should have no rows. Sorry about the dumb question, but with this kind of knowledge, why haven't it been fixed in the source code ? The example you gave is somewhat complicated as I don't have much knowledge of the internals in PosgreSQL. I hope someone will fix this soon, as this "tricker solution" demands exact knowledge of the layout of postgresql. If I need more of this hard core knowledge, where do I find this ? --- in the code ? Thanks for taking you time to write this, I may be forced to use it later :-), but as for now I will hope that I will be able to live with my non RI version, as I will be using some kind for application server to access these. /BL