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:

  • ID is row ID.
  • VariantID is product's variant ID.
  • OptionID is product's option ID.
  • Value is 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.

  • They way I read your question is that you want to delete a row and also keep all existing use of that row in tact. If this reading is correct you simply can't with your current model. Don't use cascading deletes, re-write to implement "copy on use" or perform "soft deletes" on your reference tables instead. – Michael Green Mar 27 '15 at 3:33
  • Do you want cascade or not? It's not at all clear what you want. Show us an example (I want this and this row deleted and these to stay) – ypercubeᵀᴹ Mar 27 '15 at 4:26
  • Hmm, I might need to reword it. What I want to do is only allow the deletion of child rows in the ProductVariantOptions table when parent rows are deleted. In other words, only allow CASCADE deletions to delete from the child table, not explicit deletions on individual rows in the child table. – Nick Bedford Mar 27 '15 at 4:30
  • Maybe remove delete privilege for that table? And/or allow deletes only through stored precedures. – ypercubeᵀᴹ Mar 27 '15 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.