SQL Auto Increment | Academic Block

SQL AUTO INCREMENT

Learn how to automatically generate unique numeric IDs for new rows using SQLite’s AUTOINCREMENT feature.

What is AUTO INCREMENT?

Auto-incrementing IDs allow the database to automatically generate a numeric value when a new row is inserted. This is commonly used for primary key columns such as customer IDs, product IDs, and order IDs.

In SQLite, the standard syntax is INTEGER PRIMARY KEY AUTOINCREMENT. SQLite can also automatically generate row IDs with INTEGER PRIMARY KEY without the AUTOINCREMENT keyword.

Basic AUTOINCREMENT Syntax

Define an integer primary key with the AUTOINCREMENT keyword. You do not need to provide the ID when inserting a new row.

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

INSERT INTO customers (customer_name, city)
VALUES
  ('Aarav', 'Delhi'),
  ('Meera', 'Pune'),
  ('Kabir', 'Jaipur');

SELECT *
FROM customers
ORDER BY customer_id;

AUTO INCREMENT with PRIMARY KEY

AUTOINCREMENT is commonly combined with a PRIMARY KEY so every row receives a unique numeric identifier.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_name TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products (product_name, price)
VALUES
  ('Wireless Mouse', 850.00),
  ('Mechanical Keyboard', 2400.00),
  ('USB Hub', 650.00);

SELECT *
FROM products
ORDER BY product_id;

Inserting Rows Without the ID

When an AUTOINCREMENT column is omitted from the INSERT statement, SQLite generates its value automatically.

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  category TEXT NOT NULL
);

INSERT INTO books (title, category)
VALUES ('SQL Fundamentals', 'Database');

INSERT INTO books (title, category)
VALUES ('HTML Essentials', 'Web');

INSERT INTO books (title, category)
VALUES ('Python Basics', 'Programming');

SELECT *
FROM books
ORDER BY book_id;

Checking the Generated ID

SQLite provides the last_insert_rowid() function to retrieve the row ID generated by the most recent INSERT on the same database connection.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_name TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO orders (customer_name, amount)
VALUES ('Riya', 3200.00);

SELECT last_insert_rowid() AS generated_order_id;

SELECT *
FROM orders;

AUTO INCREMENT with Multiple Rows

Multiple rows can be inserted at once while SQLite automatically generates a different ID for each row.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  employee_name TEXT NOT NULL,
  department TEXT NOT NULL
);

INSERT INTO employees (employee_name, department)
VALUES
  ('Dev', 'Engineering'),
  ('Anaya', 'Design'),
  ('Vikram', 'Support'),
  ('Nisha', 'Engineering');

SELECT *
FROM employees
ORDER BY employee_id;

Deleting a Row and Adding Another Row

AUTOINCREMENT prevents SQLite from reusing previously generated row IDs. After a row is deleted, a later insert receives a new higher ID.

DROP TABLE IF EXISTS inventory;

CREATE TABLE inventory (
  item_id INTEGER PRIMARY KEY AUTOINCREMENT,
  item_name TEXT NOT NULL
);

INSERT INTO inventory (item_name)
VALUES
  ('Camera'),
  ('Tripod'),
  ('Microphone');

DELETE FROM inventory
WHERE item_id = 2;

INSERT INTO inventory (item_name)
VALUES ('LED Light');

SELECT *
FROM inventory
ORDER BY item_id;

INTEGER PRIMARY KEY Without AUTOINCREMENT

SQLite has an important distinction: an INTEGER PRIMARY KEY column already receives automatically generated row IDs when a value is omitted. AUTOINCREMENT is not required for ordinary automatic ID generation.

DROP TABLE IF EXISTS categories;

CREATE TABLE categories (
  category_id INTEGER PRIMARY KEY,
  category_name TEXT NOT NULL
);

INSERT INTO categories (category_name)
VALUES
  ('Technology'),
  ('Education'),
  ('Science');

SELECT *
FROM categories
ORDER BY category_id;

Comparing INTEGER PRIMARY KEY and AUTOINCREMENT

Both approaches can automatically generate integer IDs. However, AUTOINCREMENT has additional behavior that prevents SQLite from reusing ROWIDs that have previously been assigned.

Feature	INTEGER PRIMARY KEY	INTEGER PRIMARY KEY AUTOINCREMENT
Automatic ID	Yes	Yes
Prevents reuse of previously assigned ROWIDs	Not guaranteed	Yes
Additional overhead	Lower	Slightly higher
Common choice	Yes	When non-reuse is specifically required

AUTOINCREMENT with Other Columns

An automatically generated ID can be combined with other constraints such as NOT NULL, DEFAULT, and CHECK.

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_name TEXT NOT NULL,
  seats INTEGER NOT NULL DEFAULT 30,
  fee REAL CHECK (fee >= 0)
);

INSERT INTO courses (course_name, fee)
VALUES
  ('SQL Fundamentals', 1200),
  ('Database Design', 1800),
  ('Web Development', 1500);

SELECT *
FROM courses
ORDER BY course_id;

Viewing the Generated IDs

You can simply select the primary key column to see the IDs generated by SQLite.

DROP TABLE IF EXISTS messages;

CREATE TABLE messages (
  message_id INTEGER PRIMARY KEY AUTOINCREMENT,
  message TEXT NOT NULL
);

INSERT INTO messages (message)
VALUES
  ('Welcome'),
  ('Learning SQL'),
  ('Practice makes progress'),
  ('Keep exploring databases');

SELECT
  message_id,
  message
FROM messages
ORDER BY message_id;

Resetting an AUTOINCREMENT Table

To completely reset an AUTOINCREMENT table in SQLite, you can remove the table and create it again. This also resets its internal AUTOINCREMENT sequence.

DROP TABLE IF EXISTS visitors;

CREATE TABLE visitors (
  visitor_id INTEGER PRIMARY KEY AUTOINCREMENT,
  visitor_name TEXT NOT NULL
);

INSERT INTO visitors (visitor_name)
VALUES
  ('Aditi'),
  ('Rohan'),
  ('Sana');

SELECT *
FROM visitors
ORDER BY visitor_id;

DROP TABLE visitors;

CREATE TABLE visitors (
  visitor_id INTEGER PRIMARY KEY AUTOINCREMENT,
  visitor_name TEXT NOT NULL
);

INSERT INTO visitors (visitor_name)
VALUES ('Karan');

SELECT *
FROM visitors;

Common AUTO INCREMENT Syntax

Syntax	Purpose	Example
INTEGER PRIMARY KEY	Automatically generated row ID	id INTEGER PRIMARY KEY
INTEGER PRIMARY KEY AUTOINCREMENT	Generate IDs without reusing previously assigned ROWIDs	id INTEGER PRIMARY KEY AUTOINCREMENT
last_insert_rowid()	Get the most recently generated row ID	SELECT last_insert_rowid()

Advantages of AUTO INCREMENT

Automatically generates numeric identifiers.
Reduces the need to manually assign primary key values.
Makes inserting new records easier.
Works well for tables that require unique numeric IDs.
AUTOINCREMENT prevents reuse of previously assigned ROWIDs.

Best Practices

Use INTEGER PRIMARY KEY when ordinary automatic row IDs are sufficient.
Use AUTOINCREMENT only when preventing reuse of previously assigned ROWIDs is important.
Do not manually insert IDs unless there is a specific reason to do so.
Use an integer primary key for simple surrogate identifiers.
Remember that AUTOINCREMENT does not make an ID universally unique across different tables or databases.

SQLite can automatically generate integer primary keys for new rows. The AUTOINCREMENT keyword provides additional guarantees that previously assigned ROWIDs are not reused.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.