How to update primary key

Question

Here is my problem - I have 2 tables:

1. WORKER, with columns |ID|OTHER_STAF| , where ID is primary key
2. FIRM, with columns |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.

Answer 1

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 2

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.

1. 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.

2. 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.

3. 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.