[sql-server] How to update primary key

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.

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.

You could use this recursive function for generate necessary T-SQL script.

CREATE FUNCTION dbo.Update_Delete_PrimaryKey
(
    @TableName      NVARCHAR(255),
    @ColumnName     NVARCHAR(255),
    @OldValue       NVARCHAR(MAX),
    @NewValue       NVARCHAR(MAX),
    @Del            BIT
)
RETURNS NVARCHAR 
(
    MAX
)
AS
BEGIN
    DECLARE @fks TABLE 
            (
                constraint_name NVARCHAR(255),
                table_name NVARCHAR(255),
                col NVARCHAR(255)
            );
    DECLARE @Sql                  NVARCHAR(MAX),
            @EnableConstraints     NVARCHAR(MAX);

    SET @Sql = '';
    SET @EnableConstraints = '';

    INSERT INTO @fks
      (
        constraint_name,
        table_name,
        col
      )
    SELECT oConstraint.name     constraint_name,
           oParent.name         table_name,
           oParentCol.name      col
    FROM   sys.foreign_key_columns sfkc
           --INNER JOIN sys.foreign_keys sfk
           --     ON  sfk.[object_id] = sfkc.constraint_object_id

           INNER JOIN sys.sysobjects oConstraint
                ON  sfkc.constraint_object_id = oConstraint.id
           INNER JOIN sys.sysobjects oParent
                ON  sfkc.parent_object_id = oParent.id
           INNER JOIN sys.all_columns oParentCol
                ON  sfkc.parent_object_id = oParentCol.object_id
                AND sfkc.parent_column_id = oParentCol.column_id
           INNER JOIN sys.sysobjects oReference
                ON  sfkc.referenced_object_id = oReference.id
           INNER JOIN sys.all_columns oReferenceCol
                ON  sfkc.referenced_object_id = oReferenceCol.object_id
                AND sfkc.referenced_column_id = oReferenceCol.column_id
    WHERE  oReference.name = @TableName
           AND oReferenceCol.name = @ColumnName
    --AND (@Del <> 1 OR sfk.delete_referential_action = 0)
    --AND (@Del = 1 OR sfk.update_referential_action = 0)

    IF EXISTS(
           SELECT 1
           FROM   @fks
       )
    BEGIN
        DECLARE @Constraint     NVARCHAR(255),
                @Table          NVARCHAR(255),
                @Col            NVARCHAR(255)  

        DECLARE Table_Cursor CURSOR LOCAL 
        FOR
            SELECT f.constraint_name,
                   f.table_name,
                   f.col
            FROM   @fks AS f

        OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @Constraint, @Table,@Col  
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            IF @Del <> 1
            BEGIN
                SET @Sql = @Sql + 'ALTER TABLE ' + @Table + ' NOCHECK CONSTRAINT ' + @Constraint + CHAR(13) + CHAR(10);
                SET @EnableConstraints = @EnableConstraints + 'ALTER TABLE ' + @Table + ' CHECK CONSTRAINT ' + @Constraint 
                    + CHAR(13) + CHAR(10);
            END

            SET @Sql = @Sql + dbo.Update_Delete_PrimaryKey(@Table, @Col, @OldValue, @NewValue, @Del);
            FETCH NEXT FROM Table_Cursor INTO @Constraint, @Table,@Col
        END

        CLOSE Table_Cursor DEALLOCATE Table_Cursor
    END

    DECLARE @DataType NVARCHAR(30);
    SELECT @DataType = t.name +
           CASE 
                WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar') THEN '(' +
                     CASE 
                          WHEN c.max_length = -1 THEN 'MAX'
                          ELSE CONVERT(
                                   VARCHAR(4),
                                   CASE 
                                        WHEN t.name IN ('nchar', 'nvarchar') THEN c.max_length / 2
                                        ELSE c.max_length
                                   END
                               )
                     END + ')'
                WHEN t.name IN ('decimal', 'numeric') THEN '(' + CONVERT(VARCHAR(4), c.precision) + ',' 
                     + CONVERT(VARCHAR(4), c.Scale) + ')'
                ELSE ''
           END
    FROM   sys.columns c
           INNER JOIN sys.types t
                ON  c.user_type_id = t.user_type_id
    WHERE  c.object_id = OBJECT_ID(@TableName)
           AND c.name = @ColumnName

    IF @Del <> 1
    BEGIN
        SET @Sql = @Sql + 'UPDATE [' + @TableName + '] SET [' + @ColumnName + '] = CONVERT(' + @DataType + ', ' + ISNULL('N''' + @NewValue + '''', 'NULL') 
            + ') WHERE [' + @ColumnName + '] = CONVERT(' + @DataType + ', ' + ISNULL('N''' + @OldValue + '''', 'NULL') +
            ');' + CHAR(13) + CHAR(10);
        SET @Sql = @Sql + @EnableConstraints;
    END
    ELSE
        SET @Sql = @Sql + 'DELETE [' + @TableName + '] WHERE [' + @ColumnName + '] = CONVERT(' + @DataType + ', N''' + @OldValue 
            + ''');' + CHAR(13) + CHAR(10);
    RETURN @Sql;
END
GO

DECLARE @Result NVARCHAR(MAX);
SET @Result = dbo.Update_Delete_PrimaryKey('@TableName', '@ColumnName', '@OldValue', '@NewValue', 0);/*Update*/
EXEC (@Result)
SET @Result = dbo.Update_Delete_PrimaryKey('@TableName', '@ColumnName', '@OldValue', NULL, 1);/*Delete*/
EXEC (@Result)
GO

DROP FUNCTION Update_Delete_PrimaryKey;

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.

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:

1. right click on the key
2. select Modify
3. Fold out 'INSERT And UPDATE Specific'
4. For 'Update Rule', select Cascade.
5. 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.

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.