Reset/Restart/Set a Identity Column in SQL Server / MYSQL / PostgreSQL

 

A relative common task that you will do someday in your life will be to reset an identity columns. Yes, those that autoincrement their value when a new row is inserted in the database table.

SQL SERVER

   1: dbcc CHECKIDENT ('table',reseed,0)

You need to call the TSQL function checkindent. Pass in the table name (as string), then ‘reseed’ or ‘noressed’ and last but not least the number you want to set the current identity.

Reseed should be passed in, but you can use noreseed if you dont want to the current identity value to be corrected.

But.. hey.. what does corrected means??.

Well, simply, the table will fix its internal sequence, so the next insert action will produce a number that will not cause conflicts.

If you don’t reseed depending of your table configuration and your current records, you will get the:

Checking identity information: current identity value 'some-number', current column value 'some-number'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

But be careful, anyway, if you have 100 records, and each of them have an ID from 1 to 100, and then you reseed your table to 1, you will get the conflict, because the values needs to be unique.

MYSQL

   1: ALTER TABLE tablename AUTO_INCREMENT = value

As easy as that. Simply set the autoincrement.

PostgreSQL

   1: ALTER SEQUENCE seqname RESTART WITH 1;

In PostgreSQL the tables can support n sequences. So, you need to alter the sequence that your auto numeric columns is using.

Simply use the alter clause, and set a value.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: