11

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.. enter image description here

smart boy
  • 671
  • 4
  • 11
  • 24
  • 7
    *Not working* is not a valid SQL Server error message. Btw: "*unique* and *identity* are two different concepts. Making a column an identity column does not necessarily make it unique (nor is that uniqueness enforced) –  Jun 25 '13 at 08:06
  • possible duplicate of [How to add identity to the column in SQL Server?](http://stackoverflow.com/questions/8658498/how-to-add-identity-to-the-column-in-sql-server) or http://stackoverflow.com/questions/16188278 –  Jun 25 '13 at 08:08
  • 2
    possible duplicate of [how to set auto increment after creating a table without any data loss?](http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss). I prefer this one since it shows a way to do it that doesn't require massive amounts of data movement. – Damien_The_Unbeliever Jun 25 '13 at 12:33

3 Answers3

17

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.

Community
  • 1
  • 1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • As Martin Smith is fond of pointing out, it can be done using [`SWITCH`](http://stackoverflow.com/a/6086661/15498) – Damien_The_Unbeliever Jun 25 '13 at 12:33
  • It's documented [here](http://technet.microsoft.com/en-us/library/ms191160%28v=sql.105%29.aspx) that the IDENTITY property is not considered when using `SWITCH`. And blogged about by a Microsoft employee [here](http://blogs.msdn.com/b/dfurman/archive/2010/04/20/adding-the-identity-property-to-a-column-of-an-existing-table.aspx) – Martin Smith Jan 18 '15 at 19:31
1

You can't alter the existing columns for identity.

You have 2 options,

Create a new table with identity & drop the existing table

Create a new column with identity & 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

Josh
  • 253
  • 3
  • 11
  • 5
    downvote because this is copy/past of other person answer:http://stackoverflow.com/a/1049305/307699 – nahab Aug 08 '14 at 10:43
1

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

anand
  • 503
  • 1
  • 7
  • 20
maino
  • 11
  • 1
  • In fact you CAN "convert" an existing column to an IDENTITY column; but you DO need to make sure to remove any Default Value or Binding! Slangevar! – iGanja Nov 03 '20 at 21:33