Two-way foreign key constraint in a 1:1 relation

问题:

I am using a MySQL database. In my relational data model, I've got two entities that relate 1:1 to each other. In my schema, a 1:1 relation is set up by putting a FK field in one of the two tables, that relates to the PK of the other table. Both tables have PKs and they are both auto increment BIGINTs.

I am wondering whether it would be possible to have an ON DELETE CASCADE behaviour on them that works both ways.

i.e. A 1:1 B, means that [ deleting A also deletes B ] as well as [ deleting B also deletes A ].

I realise that this may not be absolutely necessary in terms of proper application design, but I am just wondering whether it is actually possible. As far as I recall, you can't put an FK constraint on a PK.

回答1:

It'd be impossible to insert such records if you have a 2-way relationship enforced. Chicken-and-egg. Record in table #1 can't be inserted because there's no matching record in table #2, and table #2 cannot be inserted into because there's nothing in table #1 to hook to.

You can disable FK constraints temporarily (set foreign_key_checks = 0), but this should never be done in a "real" system. It's intended more for loading dumps where the table load order cannot be guaranteed.