I've inherited a database that is using an employee's serial number as parent/foreign keys in the related tables. Now, the input of said parent key is locked down to only a few users that have access to this table but I still do not feel comfortable with this being handled like this. I'd like to convert to an auto entered serial number or UUID and take any user interaction out of the picture.
There are many tables in this db and this is my first time really trying to fix the issues they're having. What steps do I need to take to set the parent key (either as an auto-entered serial number or UUID) and also fill all the new foreign keys in the child table? I've looked at ways to do this via setvariable, freezing, going to the layout of a child table, new record, setting the child field but I don't really understand how I can do this with respect to the already existing relationship with employee serial number. I'm just scared in that many of my child tables have multiple records that associate to the one parent id and I want to keep my relationships working (or not working but that's a different story!)