Geek asks and answers

Two-way foreign key restriction in a 1: 1 ratio

I am using a MySQL database. In my relational data model, I have two objects that relate to each other 1: 1. In my scheme, the 1: 1 ratio is adjusted by putting the FK field in one of two tables related to the PK of the other table. Both tables have PK, and both of them automatically increment BIGINT.

I am wondering if it is possible to have behavior ON DELETE CASCADEfor them that works in both directions.

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

I understand that this may not be absolutely necessary from the point of view of the correct design of the application, but I'm just wondering if this is really possible. As far as I remember, you cannot put an FK limit on a PC.

+5
source share
1 answer

You cannot insert such records if you have a two-way relationship. Chicken and egg. An entry in table No. 1 cannot be inserted because table No. 2 does not have a corresponding entry, and table No. 2 cannot be inserted because there is nothing in table No. 1.

You can temporarily disable FK ( set foreign_key_checks = 0) restrictions , but this should never be done on a "real" system. It was intended more for loading dumps, where the loading order of the table cannot be guaranteed.

+8
source

More articles:


All Articles