Foreign Key Constraint Problem

Question

The tables in question are:

enquiry

• id
• accepted_quote_id

supplier_enquiry

• id
• enquiry_id

quote

• id
• enquiry_id
• supplier_enquiry_id

The constraints are as follows:

• CONSTRAINT supplier_enquiry_ibfk_1 FOREIGN KEY (enquiry_id) REFERENCES enquiry (id) ON DELETE CASCADE

• CONSTRAINT quote_ibfk_1 FOREIGN KEY (supplier_enquiry_id) REFERENCES supplier_enquiry (id) ON DELETE CASCADE

• CONSTRAINT enquiry_ibfk_9 FOREIGN KEY (accepted_quote_id) REFERENCES quote (id)

So the way I expect this to work is:

1. If you delete an 'enquiry' it deletes the child 'supplier_enquiry' records
2. If you delete a 'supplier_enquiry' it deletes the child 'quote' records
3. You cannnot delete a 'quote' if an 'enquiry' references that quote's ID

The problem I'm having is when deleting an 'enquiry' record. Because it needs to delete the child records first, i.e. 'supplier_enquiry' and 'quote', if 'accepted_quote_id' references a 'quote' then the 'enquiry' can't be deleted.

Any idea how I can overcome this issue?

Answer 1

You get rid of the circular reference by making the two other tables dependant on enquiry.

So:

quote.enquiry_id references enquiry.id

And:

supplier_enquiry.enquiry_id references enquiry.id

EDIT: That might be unclear. I'm suggesting making a new constraint on the quote table that references enquiry_id to enquiry.id, then removing enquiry_ibfk_9

Answer 2

Update the reference to NULL before deleting.