Hi.
Im trying to implement a referential integrity check that I suspect there
is a much better way to do that what I have done. Basically what I have is
a "order" table and a "order items" table. I would like to make it so that
if all items are removed from the order, then the corresponding "order"
entry is removed.
e.g.:
CREATE TABLE orders (
id SERIAL,
dname TEXT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE order_items (
item_id SERIAL,
order_id INT NOT NULL,
item VARCHAR(80) NOT NULL,
PRIMARY KEY (item_id)
);
BEGIN;
INSERT INTO orders (dname) VALUES ('FOO');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'FOO ITEM #1');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'FOO ITEM #2');
INSERT INTO orders (dname) VALUES ('BAR');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'BAR ITEM #1');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'BAR ITEM #2');
COMMIT;
So we have 2 orders, each with 2 items in it. Suppose someone later comes
along and deletes all of the items in the order:
DELETE FROM order_items
WHERE order_id=1;
Ideally, I would like a trigger (or something similar) to fire after this
delete runs that does something like:
DELETE FROM orders
WHERE id IN (
SELECT id
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM order_items WHERE order_id=o.id)
);
(sort of the invers of "FOREIGN KEY (...) REFERENCES (..) ON DELETE CASCADE").
The trick seem sto be getting this into a function somehow. The delete
query does not return a value, so I am not sure how to go about doing that.
Assuming I *could* get this into an sql or plpgsql function somehow, I could
simply do:
CREATE TRIGGER tr_order_items_del AFTER DELETE ON order_items
FOR EACH ROW EXECUTE PROCEDURE del_order_items();
And I think that would solve my problem.
Am I making this overly complicated? Is there an easier way? If not,
then does anyone have any ideas how I can make this work?
Thanks.
Mike
