dang foreign key constraints

sneakyimp

forgive me, i have little or no experience in dealing with foreign keys. i can't delete records from two tables:

i tried this:

BEGIN;
DELETE FROM contact_info WHERE id=205852;
DELETE FROM company WHERE contact_info_id=205852;
COMMIT;

and i get this:

mysql wrote:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (dvi_aam052107/company, CONSTRAINT Ref_22 FOREIGN KEY (contact_info_id) REFERENCES contact_info (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

i try this:

BEGIN;
DELETE FROM company WHERE contact_info_id=205852;
DELETE FROM contact_info WHERE id=205852;
COMMIT;

and i get THIS:

MySQL wrote:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (dvi_aam052107/company_type_map, CONSTRAINT Ref_88b FOREIGN KEY (company_id) REFERENCES company (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

what the heck? it would seem according to this that deleting either record is not going to be possible.

Sxooter

Looks like you've got a circular FK reference with no deferring. Pretty much you can't delete anything once it's in, and might not be able to insert anything either.

You either need to drop one of the FK constraints, or set them to deferrable (assuming whatever db / engine you're using aupports deferable. )

this is like the things from Star Trek.

1: I always lie
2: I am now lying (lieing? I can't spell today)

Sxooter

BTW, are there other tables and FKs here besides the ones you're showing us? Might a third table be FKing in the second example and that's the issue?

Please show us your DDL for all the tables if you can.

Roger_Ramjet

If you are using FK constraints then you might as well change it to ON DELETE CASCADE to delete from both tables at once - remember, you delete from the parent table and the deletes are cascaded to the child table.