0

I have created a SQL Server database table but forgot to set auto increment in the primary key column. How can I set the auto increment to the existing primary key field?

CREATE TABLE [dbo].[STUDENT_INFO] 
  ( 
     [ROLLNO]       [INT] IDENTITY(1, 1) NOT NULL, 
     [SCHOOLID]     [INT] NOT NULL, 
     [STUDENTID]    [INT] NOT NULL, 
     [NAME]         [NVARCHAR](50) NOT NULL, 
     [AGE]          [INT] NOT NULL, 
     [GENDER]       [NVARCHAR](10) NOT NULL, 
     [ADDRESS]      [NVARCHAR](500) NULL, 
     [CONTACTNO]    [NVARCHAR](20) NOT NULL, 
     [EMAIL]        [NVARCHAR](50) NULL, 
     [ISACTIVE]     [BIT] NOT NULL, 
     [INSTRUMENTID] [INT] NOT NULL, 
     [GRADEID]      [INT] NOT NULL, 
     [DISCOUNT]     [INT] NOT NULL, 
     [STARTTIME]    [TIME](7) NOT NULL, 
     [DURATION]     [TIME](7) NOT NULL, 
     PRIMARY KEY CLUSTERED ( [STUDENTID] ASC ) WITH (PAD_INDEX = OFF, 
     STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
     ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
  ) 
ON [PRIMARY]

I am working with SQL Server Management Studio 2012.

Gidil
  • 4,137
  • 2
  • 34
  • 50
parithi
  • 270
  • 3
  • 15

1 Answers1

0

I was about to say that it can't be done. That you have to drop and recreate the primary key (or, just create the entire table from scratch, move the data over, and drop the old table).

EDIT: But depending on your version and edition of SQL Server, you may be able to use the object explorer to navigate to the table and access its design properties there. Where you can then set the column to an identity type.

However as someone pointed out, it may actually drop and recreate the table anyway in the background.

Anyway, as the OP already contains an edit to an answer the same as what I suggested at first (replacing the PK with another one, or recreating the table and moving the data there), I suppose there's no need to go further into this.

Kahn
  • 1,630
  • 1
  • 13
  • 23
  • Be careful with that link: just tried it and get a warning that the table be dropped and re-created. – Richard Jul 24 '14 at 08:16
  • when i go through GUI I got the error"this back end version is not supported to design database diagrams or tables " – parithi Jul 24 '14 at 08:24
  • please post the answer here – parithi Jul 24 '14 at 08:39
  • Since your version of SSMS doesn't support it anyway, I'll just drop the link and add a general description instead. – Kahn Jul 24 '14 at 09:02
  • @parithi You can get that error if you are using SQL Server Management Studio of a older version that the database you are connecting against. Like if SSMS is 2008 and your db is 2012. It is ok to use a later version of SSMS against previous versions of the DB. Not the other way around. – Mikael Eriksson Jul 24 '14 at 09:58