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.

Improve this question
0

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Be careful with that link: just tried it and get a warning that the table be dropped and re-created.
when i go through GUI I got the error"this back end version is not supported to design database diagrams or tables "
please post the answer here
Since your version of SSMS doesn't support it anyway, I'll just drop the link and add a general description instead.
@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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.