How to Reset Auto-Increment and Identity Columns in SQL

Posted by Kyle Hankinson July 18, 2023

---

After deleting test data or truncating a table, you often want to reset the auto-increment counter so IDs start from 1 again. Every database handles this differently.

MySQL

-- Reset to a specific value
ALTER TABLE users AUTO_INCREMENT = 1;

-- Or truncate the table (resets auto-increment automatically)
TRUNCATE TABLE users;

Check the current value:

SHOW TABLE STATUS LIKE 'users';

Important: The new value must be greater than or equal to the current maximum value in the column. MySQL will silently adjust it upward if needed.

PostgreSQL

PostgreSQL uses sequences for auto-increment (via SERIAL or GENERATED ALWAYS AS IDENTITY):

-- Reset the sequence to 1
ALTER SEQUENCE users_id_seq RESTART WITH 1;

-- Or if you don't know the sequence name
SELECT setval(pg_get_serial_sequence('users', 'id'), 1, false);

The false parameter means the next nextval() call returns 1. With true, it would return 2.

To reset based on the current max value:

SELECT setval(pg_get_serial_sequence('users', 'id'), COALESCE(MAX(id), 0) + 1, false)
FROM users;

Truncate also resets the sequence:

TRUNCATE TABLE users RESTART IDENTITY;

SQL Server

-- Reset identity to 0 (next insert will be 1)
DBCC CHECKIDENT ('users', RESEED, 0);

-- Reset to a specific value
DBCC CHECKIDENT ('users', RESEED, 100);

Check the current identity value:

SELECT IDENT_CURRENT('users');

Note: If you reseed to a value lower than existing data, you may get duplicate key errors on the next insert.

To insert a row with a specific identity value:

SET IDENTITY_INSERT users ON;
INSERT INTO users (id, name) VALUES (1, 'Alice');
SET IDENTITY_INSERT users OFF;

Oracle

Oracle uses sequences, which are separate objects:

-- There's no ALTER SEQUENCE ... RESTART in older Oracle
-- The workaround: drop and recreate
DROP SEQUENCE users_seq;
CREATE SEQUENCE users_seq START WITH 1;

-- Oracle 18c+ supports RESTART
ALTER SEQUENCE users_seq RESTART START WITH 1;

SQLite

SQLite tracks auto-increment in the sqlite_sequence table:

-- Reset for a specific table
UPDATE sqlite_sequence SET seq = 0 WHERE name = 'users';

-- Or delete the tracking row entirely
DELETE FROM sqlite_sequence WHERE name = 'users';

Note: sqlite_sequence only exists if you used AUTOINCREMENT in the table definition. If you used INTEGER PRIMARY KEY without AUTOINCREMENT, SQLite uses max(rowid) + 1 automatically and there is nothing to reset.

Quick Reference

Database	Reset Command
MySQL	ALTER TABLE t AUTO_INCREMENT = 1
PostgreSQL	ALTER SEQUENCE seq RESTART WITH 1
SQL Server	DBCC CHECKIDENT('t', RESEED, 0)
Oracle 18c+	ALTER SEQUENCE seq RESTART START WITH 1
SQLite	UPDATE sqlite_sequence SET seq = 0 WHERE name = 't'

When to Reset (and When Not To)

Do reset in development and test environments after clearing test data.

Don't reset in production. Gaps in IDs are normal and harmless. Reusing IDs can cause confusion in logs, caches, and external systems that reference old IDs. If an ID was once assigned to a deleted record, reusing it can lead to subtle bugs.

---