How to Alter a table for Identity Specification is identity SQL Server

Question

not working   ALTER TABLE ProductInProduct ALTER COLUMN Id KEY IDENTITY  1  1     Check Image  I have a table ProductInProduct is want its id should be Unique

User · Answer

You don t set value to default in a table  You should clear the option  Default value or Binding  first

User · Answer

You can t alter the existing columns for identity   You have 2 options   Create a new table with identity  amp  drop the existing table  Create a new column with identity  amp  drop the existing column  Approach 1   New table  Here you can retain the existing data values on the newly created identity column   CREATE TABLE dbo Tmp Names             Id int NOT NULL              IDENTITY 1  1         Name varchar 50  NULL       ON   PRIMARY  go  SET IDENTITY INSERT dbo Tmp Names ON go  IF EXISTS   SELECT                FROM    dbo Names        INSERT  INTO dbo Tmp Names   Id  Name               SELECT  Id                      Name             FROM    dbo Names TABLOCKX go  SET IDENTITY INSERT dbo Tmp Names OFF go  DROP TABLE dbo Names go  Exec sp rename  Tmp Names    Names    Approach 2  New column  You can   t retain the existing data values on the newly created identity column  The identity column will hold the sequence of number   Alter Table Names Add Id new Int Identity 1  1  Go  Alter Table Names Drop Column ID Go  Exec sp rename  Names Id new    ID    Column    See the following Microsoft SQL Server Forum post for more details   http   social msdn microsoft com forums en-US transactsql thread 04d69ee6-d4f5-4f8f-a115-d89f7bcbc032

User · Answer

You cannot  convert  an existing column into an IDENTITY column - you will have to create a new column as INT IDENTITY   ALTER TABLE ProductInProduct  ADD NewId INT IDENTITY  1  1     Update   OK  so there is a way of converting an existing column to IDENTITY  If you absolutely need this - check out this response by Martin Smith with all the gory details

[sql] How to Alter a table for Identity Specification is identity SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008