best way to update a customer ID number when related records

[tagteam]

My accounting department just notified me that one of the clients has the id number wrong. A number was transposed. When i try to update it , i get the error that it can't be updated because of the related records.



What is the best way to update this record?

My plan is:
1. check all the tables that may have this id field and run an update on all the different tables to get the correct number in the other tables.
2. break the relationship.
3. update the customer id field
4. re create the relationship

[June7]

This id is set a primary key in customers table and is saved as foreign key in related dependent table? Do you have relationship set in relationship builder? Do you have Enforce Referential Integrity checked? Do you have Cascade Update checked? Edit the id in customers table and it will automatically update in the related table.

[tagteam]

The relationship is set, cascade update is not checked. There are too many users in the database to check it now. is it a good idea to use cascade updates? I know it is not a good idea to use cascade deletes.

[tagteam]

It seemed a better idea to create a new record in the customers table that has the same customer name with the correct id. Then if i miss any of the updates it will still show the proper company name.

[June7]

Nothing wrong with cascade delete. It's purpose is to prevent orphan records in dependent tables. The real concern is whether deletion of parent records should even be allowed.

Use cascade update only when needed, as in your scenario. It can be dangerous if the parent or 'lookup' table is improperly used, which is not uncommon with inexperienced developers.