How to update primary key
First, we choose stable (not static) data columns to form a Primary Key, precisely because updating Keys in a Relational database (in which the references are by Key) is something we wish to avoid.
For this issue, it doesn't matter if the Key is a Relational Key ("made up from the data"), and thus has Relational Integrity, Power, and Speed, or if the "key" is a Record ID, with none of that Relational Integrity, Power, and Speed. The effect is the same.
I state this because there are many posts by the clueless ones, who suggest that this is the exact reason that Record IDs are somehow better than Relational Keys.
The point is, the Key or Record ID is migrated to wherever a reference is required.
Second, if you have to change the value of the Key or Record ID, well, you have to change it. Here is the OLTP Standard-compliant method. Note that the high-end vendors do not allow "cascade update".
Write a proc. Foo_UpdateCascade_tr @ID, where Foo is the table name
Begin a Transaction
First INSERT-SELECT a new row in the parent table, from the old row, with the new Key or RID value
Second, for all child tables, working top to bottom, INSERT-SELECT the new rows, from the old rows, with the new Key or RID value
Third, DELETE the rows in the child tables that have the old Key or RID value, working bottom to top
Last, DELETE the row in the parent table that has the old Key or RID value
Commit the Transaction
Re the Other Answers
The other answers are incorrect.
Disabling constraints and then enabling them, after UPDATing the required rows (parent plus all children) is not something that a person would do in an online production environment, if they wish to remain employed. That advice is good for single-user databases.
The need to change the value of a Key or RID is not indicative of a design flaw. It is an ordinary need. That is mitigated by choosing stable (not static) Keys. It can be mitigated, but it cannot be eliminated.
A surrogate substituting a natural Key, will not make any difference. In the example you have given, the "key" is a surrogate. And it needs to be updated.
- Please, just surrogate, there is no such thing as a "surrogate key", because each word contradicts the other. Either it is a Key (made up from the data) xor it isn't. A surrogate is not made up from the data, it is explicitly non-data. It has none of the properties of a Key.
There is nothing "tricky" about cascading all the required changes. Refer to the steps given above.
There is nothing that can be prevented re the universe changing. It changes. Deal with it. And since the database is a collection of facts about the universe, when the universe changes, the database will have to change. That is life in the big city, it is not for new players.
People getting married and hedgehogs getting buried are not a problem (despite such examples being used to suggest that it is a problem). Because we do not use Names as Keys. We use small, stable Identifiers, such as are used to Identify the data in the universe.
- Names, descriptions, etc, exist once, in one row. Keys exist wherever they have been migrated. And if the "key" is a RID, then the RID too, exists wherever it has been migrated.
Don't update the PK! is the second-most hilarious thing I have read in a while. Add a new column is the most.
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
If you are sure that this change is suitable for the environment you're working in: set the FK conditions on the secondary tables to UPDATE CASCADING.
For example, if using SSMS as GUI:
- right click on the key
- select Modify
- Fold out 'INSERT And UPDATE Specific'
- For 'Update Rule', select Cascade.
- Close the dialog and save the key.
When you then update a value in the PK column in your primary table, the FK references in the other tables will be updated to point at the new value, preserving data integrity.