I have a Product database with the following components:
ProductOptions is a list of option names for each Product, ProductVariants is a list of variants for each Product and ProductVariantOptions is the 1-to-many list of the option values for each variant and option name.
ProductVariantOptions contains the following:
IDis row ID.
VariantIDis product's variant ID.
OptionIDis product's option ID.
Valueis a string with the option's value.
So for a product with Size and Colour options, each variant must contain two rows, one specifying the size and one specifying the colour.
== Variants Table == ID = 1, ProductID = 9, Quantity = 23, BackOrderEnabled = 1 ID = 2, ProductID = 9, Quantity = 10, BackOrderEnabled = 1 == Options Table == ID = 1, Name = "Size" ID = 2, Name = "Colour" == VariantOptions Table == Variant = 1, Option = 1, Value = "10" Variant = 1, Option = 2, Value = "Red" Variant = 2, Option = 1, Value = "10" Variant = 2, Option = 2, Value = "Blue"
Is it possible to ensure that child rows in the VariantOptions table can only be deleted when either of their parents are? In other words, I'm not allowed to explicitly delete a row in that child table, only when the parent rows perform their cascade delete.
I need all child rows to exist unless either Options or Variants are deleted, causing their foreign keys to cascade.