Delete Order When Referential Integrity Is Active

From: "CN" <cnliou9(at)fastmail(dot)fm>
To: pgsql-general(at)postgresql(dot)org
Subject: Delete Order When Referential Integrity Is Active
Date: 2002-11-26 02:57:39
Message-ID: 20021126025738.32E752FD12@server4.fastmail.fm
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

(Please first pardon me for the repost as my previous post to google
groups seems to have not been populated to this mailing list.)

Hi!

Is it guaranteed or not to delete the rows from referencing table before
the referenced table when referential integrity constraint is applied?
For example:

CREATE TABLE MasterTable (mc1 TEXT PRIMARY KEY, UnitPrice INTEGER);

CREATE TABLE DetailTable (
dc1 TEXT
,customer TEXT
,quantity INTEGER
,PRIMARY KEY (dc1,customer)
,CONSTRAINT MyConstraint FOREIGN KEY (dc1) REFERENCES MasterTable (mc1)
ON UPDATE CASCADE ON DELETE CASCADE
);

Now, when a SQL like

DELETE FROM MasterTable WHERE mc1 = 'a';

is issued, will the rows in DetailTable be deleted before the row in
MasterTable having the value 'a' in both mc1 and dc1?

I am asking this unusual question because I have a delete trigger
associated with DetailTable to be fired before the delete from
MasterTable. As in this example, if master row is deleted before detail
rows, then this trigger function, associated with DetailTable, will be
unable to find UnitPrice in MasterTable.

If the answer is negative, then my next question would be:

"Is there any plan to implement this feature?"

I would be grateful for any instruction.

CN--
http://fastmail.fm - Choose from over 50 domains or use your own

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-11-26 03:21:26 Re: vacuum strategy
Previous Message scott.marlowe 2002-11-25 23:48:12 Re: vacuum strategy