Re: integrity and inherit

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(dot)Elphick(at)lfix(dot)co(dot)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