Primary Key Change 1

[marcoman44]

I am in a situation were external factors are forcing me to change the Primary Key of my main table. The ID (Member_ID) is now only used for a segment of the business. The Member_ID field is the Foreign Key in multiple tables. The goal is to make Member_ID an int(autonumber). I already created a new field and updated Member_ID1 (There is also a Member_ID2 which is forcing me into this situation.) with the old Member_ID. I am just looking for some advise when updating the ID. I don't think I can do this through an update query.

Thanks

 

[SQLSister]

first if you have cascading update enabled on all the foreign key tables you can change the main table and all the others will change.

If not, then you need to know exactly what tables are affected and change them first, then the main table. In this case you may need to temporarily disable referntial integrity.

Suggestions for handling this, beofre you start make a backup. next do this during the non-worrk hours if possible. You don;t want to be doing this while people are adding and changin records. Tell the users that the system is going down for maintenance (and pick the least busy persiod if you are in 24 hour mode) and then set the database to single user until you are finished.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief