SQL — Auto Increment

What is AUTO_INCREMENT?

AUTO_INCREMENT automatically generates a unique number for a column each time a new row is inserted. It's commonly used for primary keys.

MySQL Syntax

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL
);

When you insert without specifying id:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

-- id will be 1, 2 automatically
SELECT * FROM users;

Inserting with Explicit ID

You can still set the ID manually if needed:

INSERT INTO users (id, name, email) VALUES (100, 'Charlie', 'charlie@example.com');
-- Next auto-generated id will be 101

PostgreSQL: SERIAL and GENERATED

PostgreSQL doesn't use AUTO_INCREMENT. Instead:

-- Using SERIAL (auto-creates a sequence)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Using GENERATED (modern SQL standard)
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

SQL Server: IDENTITY

CREATE TABLE users (
  id INT IDENTITY(1, 1) PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
-- IDENTITY(1, 1) means start at 1, increment by 1

SQLite: ROWID

Every SQLite table has an implicit rowid column. To use it as a visible column:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

Resetting the Counter

-- MySQL: reset to 1
ALTER TABLE users AUTO_INCREMENT = 1;

-- PostgreSQL: reset the sequence
ALTER SEQUENCE users_id_seq RESTART WITH 1;

AUTO_INCREMENT with Composite Keys

CREATE TABLE order_items (
  order_id INT,
  item_number INT AUTO_INCREMENT,
  product VARCHAR(100),
  PRIMARY KEY (order_id, item_number)
);

Practical Example

CREATE TABLE blog_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  body TEXT,
  author_id INT NOT NULL,
  published_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (author_id) REFERENCES users(id)
);

INSERT INTO blog_posts (title, body, author_id)
VALUES ('My First Post', 'Hello world!', 1);

-- id is auto-generated
SELECT LAST_INSERT_ID(); -- MySQL: returns the new id

Best Practices

• Always use AUTO_INCREMENT (or equivalent) for primary keys
• Never use natural keys (email, username) as primary IDs — they can change
• Use BIGINT AUTO_INCREMENT if you expect millions of rows
• Let the database handle ID generation — don't generate IDs in application code

Practice

1. Create a products table with auto-incrementing IDs
2. Insert 5 products without specifying IDs
3. Use LAST_INSERT_ID() or RETURNING to get the generated ID