How to update primary key

Question

Here is my problem - I have 2 tables   WORKER  with columns  ID OTHER STAF    where ID is primary key 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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  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  lt  gt  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  lt  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How to update primary key

Re the Other Answers

Examples related to sql-server

Examples related to sql-server-2008

Examples related to foreign-keys

Examples related to composite-primary-key