SQL Auto Increment

Generate ids automatically.

✍️ 3 exercises + code challenge in this lesson

❮ Previous Next ❯

An auto-increment column gets a new unique number automatically for every inserted row.

MySQL: id INT AUTO_INCREMENT PRIMARY KEY
SQL Server: id INT IDENTITY(1,1) PRIMARY KEY
PostgreSQL: id SERIAL PRIMARY KEY or GENERATED ALWAYS AS IDENTITY
SQLite: id INTEGER PRIMARY KEY (optionally AUTOINCREMENT)

Leave the column out of the INSERT and the database fills it in.

Key Takeaways: SQL Auto Increment

Mastering SQL Auto Increment helps you write clean and reliable SQL code. Follow standard conventions, test edge cases, and keep logic simple. Try running the code example below and complete the exercises to build confidence.

SQL Auto Increment Code Example

SQL

CREATE TABLE Tickets (
  TicketID INTEGER PRIMARY KEY AUTOINCREMENT,
  Subject  TEXT
);

INSERT INTO Tickets (Subject) VALUES ('Login issue'), ('Payment failed'), ('New feature');
SELECT * FROM Tickets;
SELECT last_insert_rowid() AS LastID;

Free sign-in required · Google login available

SQL Auto Increment Practice Exercises

Check what you learned in “SQL Auto Increment”.

/ 3 solved

Fill in the blank

Generate ids automatically (SQLite).

TicketID INTEGER PRIMARY KEY

✓ Correct! Not quite. Try again.

Multiple choice

In MySQL the keyword is…

AUTO_INCREMENT AUTOINCREMENT IDENTITY SERIAL

✓ Correct! Not quite. Try again.

Code challenge

Create table Notes (NoteID INTEGER PRIMARY KEY AUTOINCREMENT, Body TEXT), insert 'a', 'b', 'c', then select NoteID, Body.

Write the code in the live editor. Your answer is checked automatically.

Free sign-in required · Google login available

to save your exercise progress.

Finished this lesson?

Mark it complete to track your progress.

❮ Previous

SQL Primary & Foreign Keys

Next ❯

SQL Indexes

Stuck on something?

Ask the community in the CodesInTech forum.

Need a developer?

Learning on your own is great — and when you need professional help, our team builds custom software, websites and mobile apps for clients worldwide.

Explore Our Services

SQL Auto Increment

💡 Key Takeaways: SQL Auto Increment

SQL Auto Increment Code Example

SQL Auto Increment Practice Exercises

Key Takeaways: SQL Auto Increment