Cannot add Cascase Delete - Multiple Cascade Paths

• patrick ryan-390269

  SSC Veteran

  Points: 233

  More actions

  February 10, 2009 at 6:27 am

  #205201

  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?

• Gift Peddie

  SSC Guru

  Points: 73570

  More actions

  February 10, 2009 at 7:45 am

  #941214

  Cascade DELETE is DRI(declarative referential integrity) which just means if A references B, B must exist. So when you need to Cascade DELETE and Cascade UPDATE past the first foreign key you need a DRI Trigger because the automatic version in most RDBMS should not go past one foreign key.

  So you can do what you want with a DRI Trigger.

  Kind regards,
  Gift Peddie