How to update primary key

Question

Here is my problem: I have 2 tables:

1.WORKER, with coloumns
|ID|OTHER_STAF|
, where ID is primary key, and
2.FIRM, with coloumns
|FPK|ID|SOMETHING_ELSE|

, where combination FPK and ID make primary key, and also ID is a foreign key referenced to WORKER.ID (not null, and must have same value as in WORKER).

I want to make stored procedure UPDATE_ID_WORKER, where I would like to change the value of specific ID in WORKER, and also in all instances of specific value of ID in FIRM.

stored procedure:

........ @id .. ???? ........

Thanks for every advice...

Answer 1

Don't update the primary key. It could cause a lot of problems for you keeping your data intact, if you have any other tables referencing it.

Ideally, if you want a unique field that is updateable, create a new field.

Answer 2

You shouldn't really do this but insert in a new record instead and update it that way.
But, if you really need to, you can do the following:

• Disable enforcing FK constraints temporarily (e.g. ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL)
• Then update your PK
• Then update your FKs to match the PK change
• Finally enable back enforcing FK constraints

Answer 3

When you find it necessary to update a primary key value as well as all matching foreign keys, then the entire design needs to be fixed.

It is tricky to cascade all the necessary foreign keys changes. It is a best practice to never update the primary key, and if you find it necessary, you should use a Surrogate Primary Key, which is a key not derived from application data. As a result its value is unrelated to the business logic and never needs to change (and should be invisible to the end user). You can then update and display some other column.

for example:

BadUserTable
UserID     varchar(20) primary key --user last name
other columns...

when you create many tables that have a FK to UserID, to track everything that the user has worked on, but that user then gets married and wants a ID to match their new last name, you are out of luck.

GoodUserTable
UserID    int identity(1,1) primary key
UserLogin varchar(20) 
other columns....

you now FK the Surrogate Primary Key to all the other tables, and display UserLogin when necessary, allow them to login using that value, and when they need to change it, you change it in one column of one row only.