how should data integrity be maintained in a situation where a foreign key relationship could be deleted

Let's say I have a table of PlantGroups. They are dependent on the table Plants and have the foreign key PlantId. Each PlantGroup only contains one Plant.

PlantGroups
==========
...data...
PlantId

Plants
======
PlantId

If the record in the table Plants is deleted, or is requested to be deleted, what process should be taken to ensure the data integrity of PlantGroups?

Moreover, what if other data in the corresponding PlantGroups record (that was dependent on the Plants record which was requested for deletion) is still relevant and worth keeping?

Solution

I am going to go with Option Other. Although not defined in any Data Integrity guides I have read, including Oracles (who only lists NULL, CASCADE, RESTRICT, NO ACTION, DEFAULT as options), I am going to roll my own.

Here is what is going to happen unless someone else convinces me of a better approach.

PlantGroups
===========
... data  ...
PlantId

Plant
=====
PlantId
bool isActive

On delete of Plant isActive will be set to false. This will cause a lot of extra logic to be included in the management of the database but will allow for a shallow delete of these dependent fields. A cascade delete option will perhaps sit as a final option somewhere to deal with deprecated data which is over a certain amount of years old.

Alternatively

I could also have a second database which was the same schema as the first and held deleted data.