Post reply

Cannot add Cascase Delete - Multiple Cascade Paths

  • 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?

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply