Update&Delete rules: nullifying

[JohnGo]

Hi, in relationships you can have three types to enforce relationships:
cascading : delete all foreign keys when Primary key is deleted
Restricted : you can't delete a PK when a parent has the value
nullifying : set the Child foreign key to null when the related PK has been deleted

It seems access doesn't have nullifying options, they need to be additionally coded. Is it possible to nullify a relationship within the standard fucntionality of Access?

 

[maxmangion]

nullifying : set the Child foreign key to null when the related PK has been deleted

Do you mean that you want orphan records in your database?

 

[JohnGo]

Do you mean that you want orphan records in your database?

Yes, like you say it orphans , some DBMS provide a nullify relationship option. Currently I've overcome the 'problem' of cascaded delete referential integrity' by adding a status field active/inactive throughout the database. In this way the user view will not show inactive data. My combo boxes will only show data set to active, 'out-of-date' data will not be shown.
When reporting there's another approach to inactive data. For instance a customer set to inactive might have had a big share in the years balance.

As you know, some users change their mind often so active/inactive provides them the solution to reactivate old data.

 

[JohnGo]

Ok, I found out how it should be implemented:

Cascaded --> define both updating and delete
Restricted --> Only define cascaded updating, don't define delete; access will tell you when a record with childs will be deleted it isn't possible
Nullifying --> simply don't define a relationship.