Re: integrity and inherit - Mailing list pgsql-novice

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