one-to-many relation 1

[brianpercival]

a very simple q.

I have a table A, table B connected by a one-to-many (respectively). And referential integrity is enforced. (no cascade delete).

Now when I try to delete a record from A, it fails saying records in tableB are referring to this. How do I work around this? Something like, i should be able to delete the record and perhaps make the corresponding field in the record in table B as blank? How can I Do it in access?

regards,
Brian

 

[PHV]

You really want to keep orphaned records in table B ?

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

 

[brianpercival]

yes.. cause table B is like the main table.. say... table B has addresses and table A has zip codes. this is just an example. If one of the available zip code list is deleted, I still would want to keep my address, say with blank zipcode.

regards,
Brian

 

[PHV]

So, be sure that the TableB.zipcode field allow null and then you may update this field to Null before the delete in TableA.

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

 

[brianpercival]

ya.. that would work. but is there no automatic "replace with blah on delete" in access??

 

[PHV]

If you want to play with Triggers you have to consider a "real" database, not access.

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