Is it safe to add IDENTITY PK Column to existing SQL SERVER table?-CodePudding

After rebuilding all of the tables in one of my SQL SERVER databases, into a new database, I failed to set the 'ID' column to IDENTITY and PRIMARY KEY for many of the tables. Most of them have data.

I discovered this T-SQL, and have successfully implemented it for a couple of the tables already. The new/replaced ID column contains the same values from the previous column (simply because they were from an auto-incremented column in the table I imported from), and my existing stored procedures all still work.

Alter Table ExistingTable
Add NewID Int Identity(1, 1)
Go

Alter Table ExistingTable Drop Column ID
Go

Exec sp_rename 'ExistingTable.NewID', 'ID', 'Column'

--Then open the table in Design View, and set the new/replaced column as the PRIMARY KEY
--I understand that I could set the PK when I create the new IDENTITY column

The new/replaced ID column is now the last column in the table, and so far, I haven't ran into issues with the ASP.Net/C# data access objects that call the stored procedures.

As mentioned, each of these tables had no PRIMARY KEY (nor FOREIGN KEY) set. With that in mind, are there any additional steps I should take to ensure the integrity of the database?

I ran across this SO post, which suggests that I should run the 'ALTER TABLE REBUILD' statement, but since there was no PK already set, do I really need to do this?

Ultimately, I just want to be sure I'm not creating issues that won't appear until later in the game, and be sure the methods I'm implementing are sound, logical, and ensure data integrity.

I suppose it might be a better option to DROP/RECREATE the table with the proper PK/IDENTITY column, and I could write some T-SQL to dump the existing data into a TEMP table, then drop/recreate, and re-populate the new table with data from the TEMP table. I specifically avoided this option as it seems much more aggressive, and I don't fully understand what it means for the Stored Procedures/Functions, etc., that depend on these tables.

Here is an example of one of the tables I've performed this on. You can see the NewID values are identical to the original ID.enter image description here

CodePudding user response：

Give this a go; it's rummaged up from a script we used a few years ago in a similar situation, can't remember what version of SQLS it was used against.. If it works out for your scenario you can adapt it to your tables..


SELECT MAX(Id) 1 FROM causeCodes -- run and use value below

CREATE TABLE [dbo].[CauseCodesW]( [ID] [int] NOT NULL IDENTITY(put_maxplusone_here,1), [Code] [varchar](50) NOT NULL, [Description] [varchar](500) NULL, [IsActive] [bit] NOT NULL )


ALTER TABLE CauseCodes SWITCH TO CauseCodesW;

DROP TABLE CauseCodes;

EXEC sp_rename 'CauseCodesW','CauseCodes';

ALTER TABLE CauseCodes ADD CONSTRAINT PK_CauseCodes_Id PRIMARY KEY CLUSTERED (Id);

SELECT * FROM CauseCodes;

You can now find any tables that have FKs to this table and recreate those relationships..