Preventing Deletions

[burritonator]

I have two tables called "Devices" and "DeviceTypes". Records in the Devices table have a field that contains the Primary Key of one of the records in the DeviceTypes table. I want to make sure that records don't get deleted from DeviceTypes if there are any records in Devices that are still associated with them.

I set up a Relationship between the two tables and turned on Referential Integrity, but it actually imposes more restrictions than I actually want. The Relationship does prevent the deletion of records from DeviceTypes if there are related records in Devices, which is what I want. However, when the user adds a new record to Devices, it forces them to set a value for the field that points to the DeviceTypes table, which I *don't* want. I want for the user to be able to leave that field blank if desired, as it isn't a required field (although the Relationship has turned it into one).

So, is there a way that I can prevent deletions from DeviceTypes when a related record exists in Devices, without requiring a device type to be set for every record that is added to the Devices table?

Thanks,
Burrito

 

[PHV]

Maybe the Foreign Key is defined as mandatory in table design view ?
You may also define the relation as outer join.
Or perhaps simplier, why not create a blank Device type ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Burrito

I understand exactly what you are trying to do.

It is possible to use code to prevent deletion. It is a bit of work.

I suggest you still force the referenctial integrity, but as a work-around, create a DeviceType "N/A" or "Skipped" or something similar. You can change the later.

Richard

 

[RoyVidar]

Just wondering, on my setups, enforcing referential integrity, but keeping the foreign keys required property = No, I'm allowed to create main records without child records, just as PHV mentiones. Only thing to remeber, is that you can't put any value in that field, it must be Null. If the field has some kind of default value (often 0 for numerics) then it will point to either that record or give a "You canot add or change...".

That said, I often use the methods described by both PHV and Willir of keeping a "dummy" record (and default to it), so that it is both visually evident for the user that no selection is made (often thru a combo) and a help for myself when I'm later to retrieve the records, and forget using outer joins;-)

Roy-Vidar