Foreign key constraint delete fire order - Mailing list pgsql-general

Hi!

I wish foreign key constraint trigger guarantees that rows in
referensing table are deleted before the rows in referenced table is
deleted.

Here are one inventory table and two transaction tables serving to
explain the reasons:

CREATE TABLE inv(
    warehouse TEXT
    ,item TEXT
    ,qty INT2
    ,PRIMARY KEY(warehouse,item)
);

CREATE TABLE master(
    xid INT2 PRIMARY KEY
    ,warehouse TEXT
);

CREATE TABLE detail(
    xid INT2
    ,item TEXT
    ,qty INT2
    ,PRIMARY KEY(xid,item)
    ,CONSTRAINT fk FOREIGN KEY (xid) REFERENCES master (xid) ON UPDATE CASCADE ON DELETE CASCADE
);

This rule (or a pair of trigger+function) tries to subtract inventory
automatically when rows are deleted from detail table:

CREATE RULE rd AS ON DELETE TO detail DO
(
  UPDATE inv SET qty=qty-OLD.qty WHERE warehouse
  =(SELECT warehouse FROM master WHERE xid=OLD.xid)
);

Because we might delete rows from either "master" or "detail", rule "rd"
is attached to detail table. Problem is that inventory does not decrease
if we delete a row from master table because

SELECT warehouse FROM master WHERE xid=OLD.xid

returns nothing the moment the rule is fired.

With existing fire order of the integrity foreign key constraint, the
implementation of update propagation as shown in this example becomes
very complicate. First, this constraint must not exist:

CONSTRAINT fk FOREIGN KEY (xid) REFERENCES master (xid) ON UPDATE
CASCADE ON DELETE CASCADE

Secondly, triggers along with complicate functions must be created and
attached to "master" and "detail" tables.

Does my wish make sense or violate any standard?

Best Regards,

CN
-------------
db2=# \d detail
     Table "public.detail"
 Column |   Type   | Modifiers
--------+----------+-----------
 xid    | smallint | not null
 item   | text     | not null
 qty    | smallint |
Indexes:
    "detail_pkey" PRIMARY KEY, btree (xid, item)
Foreign-key constraints:
    "fk" FOREIGN KEY (xid) REFERENCES master(xid) ON UPDATE CASCADE ON
    DELETE CASCADE
Rules:
    rd AS
    ON DELETE TO detail DO  UPDATE inv SET qty = inv.qty - old.qty
  WHERE inv.warehouse = (( SELECT master.warehouse
           FROM master
          WHERE master.xid = old.xid))
db2=# insert into inv values('w','a',20);
INSERT 0 1
db2=# insert into master values(1,'w');
INSERT 0 1
db2=# insert into detail values(1,'a',5);
INSERT 0 1
db2=# select * from inv;
 warehouse | item | qty
-----------+------+-----
 w         | a    |  20
(1 row)

db2=# delete from master;
DELETE 1
db2=# select * from inv;
 warehouse | item | qty
-----------+------+-----
 w         | a    |  20
(1 row)

--
http://www.fastmail.fm - And now for something completely different…