Foreign key constraint delete fire order

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…