Menu
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.
649
If there is a one-to-one relationship between one entity and another entity, add the key of one of the entities into the table for the other entity, thus changing it to a foreign key. The addition of a foreign key due to a one-to-one relationship can be made in either direction.
A table may have multiple foreign keys, and each foreign key can have a different parent table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.
A table can have multiple foreign keys based on the requirement.
A foreign key relationship could be one-to-one (a record in one table is linked to one and only one record in another table) or one-to-many (a record in one table is linked to multiple records in another table). Foreign keys are only supported on InnoDB tables.
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.
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
