I have the following schema in which I cannot add multiple cascade delete on some fields. I was wondering it this is normal, and if not, can someone think of a better way representing my schema?
Overview: Users r creating forecasts. They want versions of a forecast, but only certain entities are versioned and some are consistent within a forecast. For example, ForecastCountry, this will be the same for all versions of a forecast, but there can be different pricingdrugs between versions. Here is a sample schema.
Forecast PricingDrug
--------------- ----------------
ForecastID (PK) PricingDrugID (PK)
VersionID (FK)
PricingDrugName
Version
---------------- ForecastCountry
VersionID (PK) ----------------------
ForecastID (FK) ForecastCountryID (PK)
CountryID (FK)
ForecastID (FK)
Now the table in question, I cannot add cascade delete on both of the FK fields.
PricingDrugForecastCountry
----------------------------
PricingDrugForecastCountry(PK)
ForecastCountryID (FK)
PricingDrugID (FK)
I can only add cascade delete on one of the Fk. I do notice the 2 different cascade paths in the schema, so I know why, my question is there a better approach?