SQL Server How do I add Auto Increment to Existing SQL ID Primary Key? [duplicate]

zbdgwd5y  于 2023-06-28  发布在  其他
关注(0)|答案(2)|浏览(136)

This question already has answers here:

Adding an identity to an existing column (19 answers)
Closed 6 days ago.

I have an empty table that I thought was auto increment until I tried adding my first record. That's when SQL told me this:

Cannot insert the value NULL into column 'ID', table 'DB_9CF886_mcl959.dbo.Posts'; column does not allow nulls. INSERT fails.

So I tried to add the identity to it:

  1. alter table dbo.Posts ADD ID INT IDENTITY(1,1) CONSTRAINT PK_Posts1 PRIMARY KEY CLUSTERED;

That gave me this error:

Column names in each table must be unique. Column name 'ID' in table 'dbo.Posts' is specified more than once.

I could drop the table and recreate it, but I'd like to know how to FIX IT to add the identity to the ID column.

Screenshot of database: dbo.Posts

gmol1639

gmol16391#

While it's true that you cannot add IDENTITY to an existing column, and that adding IDENTITY to an existing table means adding a new column - it's also true that you don't need to use IDENTITY - nor do you need to DROP anything either.

Instead, ALTER your existing ID column to use a SEQUENCE object for new values - which has (effectively) the same end-result as IDENTITY - but this way it will preserve your existing ID column values, preserve foreign-key references, and preserve your CLUSTERED index without needing a table rebuild.

Do this:

  1. SET XACT_ABORT ON;
  2. BEGIN TRANSACTION;
  3. DECLARE @baseId int = ( SELECT ISNULL( MAX( ID ), 0 ) FROM dbo.Posts ) + 1;
  4. -- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql
  5. CREATE SEQUENCE dbo.PostsIdSequence START WITH @baseId INCREMENT BY 1;
  6. ALTER TABLE dbo.Posts
  7. ADD CONSTRAINT DF_Posts_Sequence DEFAULT NEXT VALUE FOR dbo.PostsIdSequence FOR "ID";
  8. COMMIT TRANSACTION;
展开查看全部
0s7z1bwu

0s7z1bwu2#

Below is an example that uses SWITCH to move data into another table of identical schema that already has the IDENTITY .

  1. --create sample table and data
  2. CREATE TABLE dbo.AddIdentityExample(
  3. IncrementalColumn int NOT NULL
  4. CONSTRAINT PK_AddIdentityExample PRIMARY KEY
  5. , OtherData varchar(20) NULL
  6. );
  7. CREATE INDEX idx_AddIdentityExample_OtherData
  8. ON dbo.AddIdentityExample(OtherData);
  9. INSERT INTO dbo.AddIdentityExample VALUES
  10. (1, 'Sample data one')
  11. , (2, 'Sample data two')
  12. , (3, 'Sample data three');
  13. GO
  14. SET XACT_ABORT ON;
  15. BEGIN TRY
  16. BEGIN TRAN;
  17. --create staging table with same schema, indexes, and constraints
  18. CREATE TABLE dbo.AddIdentityExampleStaging(
  19. IncrementalColumn int IDENTITY NOT NULL --IDENTITY column property added
  20. CONSTRAINT PK_AddIdentityExampleStaging PRIMARY KEY
  21. , OtherData varchar(20) NULL
  22. );
  23. CREATE INDEX idx_AddIdentityExampleStaging_OtherData
  24. ON dbo.AddIdentityExampleStaging(OtherData);
  25. --move data from source table into new table
  26. ALTER TABLE dbo.AddIdentityExample
  27. SWITCH TO dbo.AddIdentityExampleStaging;
  28. DROP TABLE dbo.AddIdentityExample;
  29. --rename table
  30. EXEC sp_rename
  31. @objname = N'dbo.AddIdentityExampleStaging'
  32. , @newname = N'AddIdentityExample'
  33. , @objtype = 'OBJECT';
  34. --rename constraints
  35. EXEC sp_rename
  36. @objname = N'dbo.PK_AddIdentityExampleStaging'
  37. , @newname = N'PK_AddIdentityExample'
  38. , @objtype = 'OBJECT';
  39. --rename indexes
  40. EXEC sp_rename
  41. @objname = N'dbo.AddIdentityExample.idx_AddIdentityExampleStaging_OtherData'
  42. , @newname = N'idx_AddIdentityExampleStaging_OtherData'
  43. , @objtype = N'INDEX';
  44. --seed IDENTITY with current max column value
  45. DBCC CHECKIDENT(N'dbo.AddIdentityExample');
  46. COMMIT;
  47. END TRY
  48. BEGIN CATCH
  49. IF @@TRANCOUNT > 0 ROLLBACK;
  50. THROW;
  51. END CATCH;
  52. GO
展开查看全部

相关问题