-1

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

Jeff King
  • 1
  • 3
  • 1
    What are you actually asking here? Your title asks how to add an `IDENTITY` column to your table, but the question demonstrates you already know how to do that and are concerned about the integrity of your database. Your database, technically, has *little* integrity as there are no Primary Keys (PKs), and thus no Foreign Keys (FKs). When you add the new `IDENTITY` column though, the values assigned are going to be arbitrary; they won't have any relationship to the existing non-PK "`ID`" column. So if you *did* have any "psuedo" relationships, without using an FK, it's unlikely it'll match now. – Thom A Oct 24 '21 at 14:08
  • Right, so you already have a column that has usable primary key values in, and probably other tables with their records already link back to it )ie you have an Address record with ID 123 and a Person ID 111 whose AddressId is 123). Why don't you just make this column the PK, set it as identity and start it from max+1 of the values that are already there? Making another column is going to be a big headache because it'll get a bunch of different auto inc values, then you'll need to wire the child records up using the old references and insert the new references and.. it all sounds very tedious! – Caius Jard Oct 24 '21 at 14:15
  • Fair point. Yes, I do know how. The question is.. is the method I'm implementing reasonable enough to ensure the integrity? I get that it currently has no integrity; the original table did have PK's.. I just failed to set them when I imported into this new database. Once I set these PK's, is the method I used to do so worthy of ensuring this integrity? Additionally, the existing non-PK, non-IDENTITY ID's are sequential, and have matched exactly for each of the tables I've already performed this on. I would expect the same result for the other tables. But I do understand your point. – Jeff King Oct 24 '21 at 14:17
  • But I still don't get why you're trying to recreate a whole new identity system for all your tables that already have a potentially usable, currently inactive, identity system – Caius Jard Oct 24 '21 at 14:18
  • have you ever thought about existing DML statements that were already built with the old schema ? using `IDENTITY` with an existing column could break those DMLs if exists in them. – iSR5 Oct 24 '21 at 14:18
  • @CaiusJard - The existing ID values are sequential, starting with 1. So far, the new ID columns are matching exactly, as they also start with 1. Each of these tables have the same situation. – Jeff King Oct 24 '21 at 14:19
  • @CaiusJard Because I can't modify an existing to column to make it an IDENTITY column. I want this column to be auto-incremented. – Jeff King Oct 24 '21 at 14:20
  • @CaiusJard Your solution sounds perfect, but my understanding is I cannot make this existing column an IDENTITY column. Based on your suggestion, are you saying that I can change it to IDENTITY, if I set it to "start from max+1"? – Jeff King Oct 24 '21 at 14:42
  • Correct, a column cannot be altered be be an `IDENTITY` column, nor can you `UPDATE` the value of an `IDENTITY` column. If you *must* go down this path, you'd be better off adding all the `IDENTITY` columns, then updating all the pseudo FK columns with the new `IDENTITY` values, make them proper FK (and the `IDENTITY` a PK or at least `UNIQUE`) and *finally* dropping your old pseudo-PK columns and rename your new PK columns. This won't be quick, nor un-invasive. – Thom A Oct 24 '21 at 14:43
  • You probably need to also run `DBCC CHECKIDENT` on each table. @CaiusJard You cannot set an existing column to `IDENTITY` see fiddle https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=fd7653232ef526999f990c692d2d74eb – Charlieface Oct 24 '21 at 14:43
  • @Larnu Fortunately, in each case, the existing ID's would match any new IDENTITY column, as each of them are in perfect sequential order, starting with 1. – Jeff King Oct 24 '21 at 14:52
  • 1
    Unlikely, @JeffKing , the values would be assigned completely arbitrary. There is no guarantee, none, that they will be the same. This is especially true when it appears you have no PK, and likely no clustered index. – Thom A Oct 24 '21 at 14:55
  • 2
    Does this answer your question? [Adding an identity to an existing column](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) specifically this answer https://stackoverflow.com/a/1730868/14868997 – Charlieface Oct 24 '21 at 15:02
  • @Larnu I see what you mean. For the tables I've already performed this on, the values ended up being identical. I just performed it on another table, with the same results. However, each of them only have 5-10 rows each, this is true for each of the tables where I failed to set PK and IDENTITY. Would you agree that if the new IDENTITY values end up being the same, that this solution is fine? If not, what do you recommend? Rebuild/Repopulate the entire database again? I'm not afraid to do that, if I absolutely have to. – Jeff King Oct 24 '21 at 15:25
  • @Larnu I've edited the post to show an image of the one of the tables where I used this logic with success. I appreciate your input. – Jeff King Oct 24 '21 at 15:31
  • *" Would you agree that if the new IDENTITY values end up being the same, that this solution is fine? "* you have no way of making this happen though. If you did, sure, but no such solution exists. – Thom A Oct 24 '21 at 15:35
  • Yes, I didn't explain very week what I meant there- lot going on locally. I mean make a table that mimics the schema of the problem table but with an identity that is max+1 and then switch it for the one with the data.. – Caius Jard Oct 24 '21 at 15:35
  • @Charlieface That post is where I got the logic I'm using from. Not the specific post you mentioned. Each of my tables only contain a few rows, and I figured the syntax I used was the best option. But, I'm not completely certain, so I posted this question. – Jeff King Oct 24 '21 at 15:36
  • Or add a SEQUENCE and a default instead of an IDENTITY column. – David Browne - Microsoft Oct 24 '21 at 15:38
  • @Larnu Is it purely coincidental that I'm ending up with the same values with these tables where this has been executed? – Jeff King Oct 24 '21 at 15:39
  • @DavidBrowne-Microsoft That seems like a great option. I'll read up on that. Are you saying I can change the existing ID column to a SEQUENCE with a default? – Jeff King Oct 24 '21 at 15:41
  • Yes, it's just, for lack of better words, blind luck that they are the same; though with a table with handful of rows, the chances are much higher than with a table with thousands of rows (or more). There's only so many ways you can order the numbers 1-4. – Thom A Oct 24 '21 at 15:46
  • That being said, is there any issue with the new ID column being the last column in the table, instead of the first? Nowhere am I referring to the column by its index, but that doesn't mean something internal doesn't.. and that is one of the questions I have about this. – Jeff King Oct 24 '21 at 15:54
  • Order of columns doesn't matter, but if you switch then you can have it be first.. post your existing table def? – Caius Jard Oct 24 '21 at 16:18
  • @CaiusJard Thanks. I prefer them to be first, but can live with them being last, once I fully understand the pros/cons. – Jeff King Oct 24 '21 at 16:20
  • Here's the CREATE TO script for one othe tables: CREATE TABLE [dbo].[CauseCodes]( [ID] [int] NOT NULL, [Code] [varchar](50) NOT NULL, [Description] [varchar](500) NULL, [IsActive] [bit] NOT NULL ) ON [PRIMARY] GO – Jeff King Oct 24 '21 at 16:28

1 Answers1

1

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • This is great, thank you! For the tables that have many rows, I'll use this method, and post back how it goes. – Jeff King Oct 24 '21 at 17:23
  • Don't forget SSMS can help you script tables; right click "script as .. create .. to new window". Wiring relationships up via drag and drop can be done in a database diagramming window. – Caius Jard Oct 24 '21 at 17:54
  • I did know about the CREATE TO scripting. I did not know about the Diagramming window, and using a GUI to create relationships. I came from the MS Access world; is this like the function in Access where you can drag columns to create relationships? – Jeff King Oct 24 '21 at 18:05
  • Pretty much; SSMS always seems to be a bit of an odd mix of functionality that never quite ties together; for example if you create a table column and refresh the explorer tree, intelligence still doesn't know about it too you press Ctrl Shift R; the database diagramming is another oddity that is like it's own disconnected little world that can go out of sync with the other table designers but generally if you just use it and it alone, in a session, to do design alterations it works out well. Drag and drop brings a window where you confirm relationships, I think you drag from parent to child – Caius Jard Oct 24 '21 at 20:01
  • The table view can also be used for redesigning columns etc. if you switch the view per table from the normal compact mode, to standard .. – Caius Jard Oct 24 '21 at 20:01
  • Personally I would just use `DBCC CHECKIDENT` instead of `SELECT MAX` – Charlieface Oct 24 '21 at 21:42