sqlsql-serversql-updatesql-insertprimary-key

Re-Insert/Update primary key value after a mistake (restoration)


I encounter a problem in SQL Server.

We have a table Country with N_Country as its primary key. Please note the table have like 10 columns but I am only showing these two for the example:

N_Country Name
1 United Kingdom
2 España
3 France
4 Deutschland
5 Italia

However, we do not know why, but the 3rd row (France) got deleted. Now, we only have primary keys 1/2/4/5 and no longer 3.

I can not add a row into this table as it would make a country with primary key 6, as I would need to update all my other tables which had "N = 3" to "N = 6", which is a lot of work (and I "do not know all the tables" that would need that update).

The "fastest/best" two solutions I see are:

  1. I create a new row (N = 6, Name = France) and then I

     UPDATE Country
     SET N_Country = 3 
     WHERE N_Country = 6
    
  2. I directly do

     INSERT INTO Country (N_Country, Name) 
     VALUES (3, 'France')
    

Both should be "safe" as I am just restoring a deleted value. I never update primary keys (as it is a really bad idea) but here I am stuck with the error

IDENTITY_INSERT is defined to OFF

I saw from related post the SET IDENTITY_INSERT aTable ON

I would like to know if I can use something like this?

SET IDENTITY_INSERT Country ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO Country (N_Country, Name) 
VALUES (3, 'France')
GO

SET IDENTITY_INSERT aTable OFF
GO

And also if you think this is "safe"? (I guess so) - But if my table Country has 10 columns, should I "INSERT" all 10 columns or can I just INSERT the two main ones, and then update later?

Thank you for your help.


Solution

  • In case you want to insert a specific value on an identity column then first

    SET IDENTITY_INSERT <schema>.<table> ON;
    

    This allows with a follow up insert statement to insert a wanted value for the identity column.

    Very important afterwards:

    SET IDENTITY_INSERT <schema>.<table> OFF;
    

    Only one ON state can exist across all tables in a database at a time.