Foreign keys and constraints on pivot tables when deleting a cascade
I designed my database structure as shown below to support the creation of multiple stores and users assigned to those stores. My tables use the InnoDB storage engine.
[user]
user_id
[user_profile]
user_profile_id
user_id
[user_to_shop] PIVOT
user_id
shop_id
[shop]
shop_id
[shop_profile]
shop_profile_id
shop_id
[category_to_shop] PIVOT
category_id
shop_id
[category]
category_id
[category_description]
category_description_id
category_id
[product_to_category] PIVOT
product_id
category_id
[product_to_shop] PIVOT
product_id
shop_id
[product]
product_id
...
...
What I want to achieve is to remove all related posts (category posts, store posts, ..., ...) when the user (user_id) is deleted.
I have created constraints for each object. So when I remove user_id (1), the engine takes care of removing user_profile (user_id (1)) as well.
ALTER TABLE `user_profile`
ADD CONSTRAINT `user_profile_cs1`
FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`)
ON DELETE CASCADE
But what should I do with pivot tables? How do I declare constraints or foreign keys on these tables to get the job done and be safe?
+3
source to share
1 answer
Create a delegation chain: add FK constraints to pivot tables ([C]) or [D] which depend on C below)
[A]
idA
dataA
[B]
idB
dataB
[C]
idC
idA
FOREIGN KEY (idA) REFERENCES A(idA)
ON DELETE CASCADE
idB
FOREIGN KEY (idB) REFERENCES B(idB)
ON DELETE CASCADE
[D]
idD
idC
FOREIGN KEY (idC) REFERENCES C(idC)
ON DELETE CASCADE
Order of operations
Delete(A) -> Delete(C) -> Delete(D)
Delete(B) -> Delete(C) -> Delete(D)
0
source to share