Click here to Skip to main content
14,640,651 members
Rate this:
Please Sign up or sign in to vote.
I want to know How to alter a Primary Key column to Auto-Increment, also it should work as Both Primary Key and auto increment field... So that the Entries can have Unique 'ID'(Primary Key Column Name)...
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 3

Hi,
You can use below query
e.g.

Create table tbl_Emplooyee
(Recordid bigint Primary key identity)
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

If once, you set a column as Primary Key Means it act as auto increment column...

Identity is the main key


http://p2p.wrox.com/sql-server-2000/59608-how-can-i-alter-table-primary-key-identity.html[^]

http://stackoverflow.com/questions/11897007/alter-table-column-for-primary-key-and-identity[^]

column_name bigint primary key identity


hey..dont worry be happy
   
v7
Comments
[no name] 23-Nov-15 1:05am
   
you have to provide seed and interval for identity column
Arasappan 23-Nov-15 1:14am
   
You give Pankaj...
Rate this:
Please Sign up or sign in to vote.

Solution 2

1. First set the column as primary.
2. Set the dataType of that column as Int.
3. Go to column Property and set Identity Specification 'Yes' and Identity Increment to 1
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

Try this script which modifies a PK column to auto increment.

DROP TABLE TEST
GO

create table test
( 
  code varchar(100),
  DESCR varchar(1000)
  CONSTRAINT P_test_code PRIMARY KEY CLUSTERED(code)
)
go


insert into test(code, DESCR)
select 'A', 'AAAA'
UNION ALL
select 'B', 'BBBBB'

go

select *
from	test
go

ALTER TABLE test DROP CONSTRAINT P_test_code
GO

ALTER TABLE test ADD CODE2 INT IDENTITY
GO


ALTER TABLE test
ADD PRIMARY KEY (CODE2)
go

select *
from	test
   
Rate this:
Please Sign up or sign in to vote.

Solution 5

You can try this:
DECLARE @id INT
SET @id = 0 
UPDATE table SET @id = id = @id + 1 


See Here for the detail: https://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100