Received: from lh00.opsion.fr (lh00.opsion.fr [212.73.208.226]) by postgresql.org (8.11.3/8.11.4) with SMTP id fAJ7I9m16575 for ; Mon, 19 Nov 2001 02:18:10 -0500 (EST) (envelope-from cnliou@eurosport.com) Received: from 10.1.1.8 [10.1.1.8] by lh00.opsion.fr; Mon, 19 Nov 2001 07:17:59 GMT Send-By: 203.66.130.107 with Mozilla/4.78 [en] (Win95; U) To: Subject: Re: Foreign Key Constraint Deletion Order From: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Date: Mon, 19 Nov 2001 07:17:59 GMT Message-id: <200111190717.3b63@lh00.opsion.fr> Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id fAJ7LhL16655 X-Archive-Number: 200111/263 X-Sequence-Number: 5645 Thanks! Stephan, > We've been having discussions on hackers about the behavior, > but it's unlikely that the rows will be deleted before > the master row. Then I am in big big trouble! Please imagine a very usual scenario: create table PurchaseMaster (book text primary key, UnitPrice float); create table PurchaseDetail ( CONSTRAINT fk_abc FOREIGN KEY (book) REFERENCES PurchaseMaster (book) on delete cascade on update cascade, primary key (book,buyer), book text, buyer text, quantity smallint ); create table HowMuchIOwe (buyer text primary key,amount float); CREATE FUNCTION MyTrigger() RETURNS opaque AS ' BEGIN UPDATE HowMuchIOwe set amount=amount-old.quantity*(select UnitPrice from PurchaseMaster where book=old.book) where buyer=old.buyer; END; CREATE TRIGGER TriggerDetail AFTER DELETE ON PurchaseDetail FOR EACH ROW EXECUTE PROCEDURE MyTrigger( ); Now when a row in PurchaseMaster is deleted by user, because: (1) getting rid of fk_abc constraint and replacing it with a custom trigger function associated with PurchaseMaster in order to delete PurchaseDetail "manually" does not work either since PurchaseMaster row may be deleted BEFORE this custom trigger function is called; (2) and row in PurchaseMaster may be deleted before PurchaseDetail. so I have no way out! CN -------------------------------------------------------- You too can have your own email address from Eurosport. http://www.eurosport.com