Changing the relationship in an active db

[JaneInMA]

I have been handed a Access 97 db that I figure was overdesigned. My problem is that the employee table has relationships with other tables with referential integrity being enforced. BUT the designer did not set up cascading updating. I can understand that cascading deleting will cause problems but no update?
The primary key is an employee ID# which this being a typical company is impossible to get from HR. Therefore the people who have been using it have been making up ID#s when necessary. Now we would like to link this to our employee table which has correct data and info in it.
However the constraints prevent us updating the employee ID#.
I discussed this with the designer and his opinion was that if I changed the relationship to allow cascading updates it would break the db relationships.
Is he being over cautious, am I being under cautious to consider this?
If you have dealt with a similar problem I would appreciate your input.

 

[nrjhano]

Does the database use inner join?

I cannot remember now, but for some reason I ended up with that choice. Worked fine until I discovered that cascaded deleting was not maintained as you mentioned. I discovered that I have fragmented leftover records of inner tables after deletion of records in an outer table. But this consequence was not vissible in my queries.

I discovered it during an attempt to recurse 3 different time perspectives. I wanted to minimise the amount of data so I included only one table to control the recursion. After removing the fragmented records the recursion was a success.

Updating is assured from my VBA program so that was no problem. So the alternative is either to assure deleting from VBA-code or go back into outerjoin and do as you suggest.

At the moment I am trying to reconfigure my backbone tables into an outer join with cascaded deletion and updating. During this process I have to restore all the links in my "Relationships"-table. On top of that I have to restore 12 out of 16 queryes this evening.

"Is he being over cautious, am I being under cautious to consider this?" Well, I am close of getting desparate if I fail to make it right this time.

Hakan

 

[JaneInMA]

We will not delete from the employee table as the legal restrictions of the records the db supports mean we must keep all our records so I didnt want to implement a cascading delete. I feel I must update the relationship but consequences are worriesome. I aim to make a complete copy of the db and implement the change and get some help to try and break it if it is going to break. Sometimes we inherit db architecture (problems) that give us extreme headaches

 

[nrjhano]

Concerning your problem. Would it possible to link your ID-table to itself. The intention being to simply add the correct references and a link to itself, thereby maintaining the present structure? Completely new ID's will be dupplicated of course.

Your comment inspired me to reconsult the documentation about Referential Integrety and Joint types, thanks! The purpose of Referential Integrety is related to slowly changing and mostly predefined data, .e.g. to identify a member as you mentioned or a property from a given set.

My backbone tables contains cascaded and comparatively free information allthough I link properties or individuals to them. Shame on me, I should skip referential integrety to my backbone and set it up for the others and in their smaller context arrange cascaded update and delete. Also I have noticed that Autonumber cannot be used for Referential Integrety purposes which is actually quite natural when come to think about it.

Anyway, I erroneously used the term inner join for the backbone, should be outer left join. My attempt was to change this to right outer join. I cannot see that I gained anything from that so I think my solution will be to maintain the left outer join. Instead I will try to control proper delete of my backbone records from program.

Hakan
Osaka, 2000-11-29

 

[JerryDennison]

Hakan,
I don't understand your statement that Autonumber doesn't work for referential integrity. I beg to differ, it works beautifully for that purpose. Referential integrity simply insures that no child record can exist without a corresponding parent record. It doesn't matter what value this link is so long as it is unique for the parent. Could you elaborate on why you think it doesn't?