CODEWITHSUNDEEP
sqlsql-serverdatabase
BrunoEarth
BrunoEarth

Reputation: 333

How to reset auto-increment in SQL Server?

I've been having this problem with my database where it kept on incrementing the id column even though it has been removed. To better understand what I meant, here is a screenshot of my gridview:

enter image description here

As you can see from the id column, everything is fine from 11 - 16. but it suddenly skipped from 25 - 27. What i want to happen is, when i remove an item, i want it to start from the last id which is 16. So the next id should be 17. I hope this makes sense for you guys.

Here is also part of the SQL script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[guitarItems]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [type] [varchar](50) NOT NULL,
    [brand] [varchar](50) NOT NULL,
    [model] [varchar](50) NOT NULL,
    [price] [float] NOT NULL,
    [itemimage1] [varchar](255) NULL,
    [itemimage2] [varchar](255) NULL,
    [description] [text] NOT NULL,
    [necktype] [varchar](100) NOT NULL,
    [body] [varchar](100) NOT NULL,
    [fretboard] [varchar](100) NOT NULL,
    [fret] [varchar](50) NOT NULL,
    [bridge] [varchar](100) NOT NULL,
    [neckpickup] [varchar](100) NOT NULL,
    [bridgepickup] [varchar](100) NOT NULL,
    [hardwarecolor] [varchar](50) NOT NULL,

    PRIMARY KEY CLUSTERED ([id] ASC)
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO

 SET ANSI_PADDING OFF
 GO

Upvotes: 1

Views: 18189

Answers (2)

samithagun
samithagun

Reputation: 693

Primary autoincrement keys in the database are used to uniquely identify a given row and shouldn't be given any business meaning. So leave the primary key as it is and add another column like guitarItemsId. Then when you delete a record from the database you may want to send an additional UPDATE statement in order to decrease the guitarItemsId column of all rows that have the guitarItemsId greater than the one you are currently deleting.

Also, remember that you should never modify the value of a primary key in a relational database because there could be other tables that reference it as a foreign key and modifying it might violate the referential constraints.

Upvotes: 2

Tomato32
Tomato32

Reputation: 2245

You can use: 

DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1);

Before using it, visit link: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql

Upvotes: 4

Related Questions