I have an employee ID based on initials+last four of ssn.

The computer can generate this automatically. The problem is that 14 other tables are linked to the Employee_ID, and if I need to update the employee_ID, because of an error in the persons name, I can`t seem to do it, and do a cascading update.

The only way I know to do it is to delete all of the relationships (RFI) between employee_ID and it`s foreign keys in other tables and use triggers., but then this means that in each of the 14 tables, I would have to check before allow updates, deletes, and inserts to find out whether or not it is a valid value.

Does anyone have any ideas? This is really a problem. It is not an issue in tables where an autonumber is the PK, because it will never change, but if I used an autonumber on employee table and had to drop and reestablish it, it would be a pain.

I need to be able to update the employee_ID and have it update the foreign keys referencing it.

I am out of ideas.

Thanks,


Shane Story, B.S.
Web Developer/Programmer

Center for Total Access (http:\www.cta.ha.osd.mil)
Building 38711
Fort Gordon, GA

DSN: 773-2396
Phone: (706) 787-2396
Pager: (706) 787-8084 1386
Fax: (706) 787-2402
Email: ShaneS@mail.cta.ha.osd.mil

================================================== ====
The views expressed in this message do not necessarily reflect the views of my employer.