Command guide

intermediate defining MySQL MariaDB SQL Server PostgreSQL SQLite

Generate Row IDs Automatically

Define an identity or auto-increment column so new rows receive numeric IDs without the insert supplying them.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Insert products without supplying the id column

The insert statements provide only name. The database fills id with 1 and 2 because the column was defined as an automatically generated key.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

Validated query

SQL

INSERT INTO
  products (name)
VALUES
  ('Widget'),
  ('Gadget');

SELECT
  id,
  name
FROM
  products
ORDER BY
  id;

Expected result

id	name
1	Widget
2	Gadget

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (
  id INT IDENTITY (1, 1) PRIMARY KEY,
  name VARCHAR(50)
);

Validated query

SQL

INSERT INTO
  products (name)
VALUES
  ('Widget'),
  ('Gadget');

SELECT
  id,
  name
FROM
  products
ORDER BY
  id;

Expected result

id	name
1	Widget
2	Gadget

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(50)
);

Validated query

SQL

INSERT INTO
  products (name)
VALUES
  ('Widget'),
  ('Gadget');

SELECT
  id,
  name
FROM
  products
ORDER BY
  id;

Expected result

id	name
1	Widget
2	Gadget

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(50)
);

Validated query

SQL

INSERT INTO
  products (name)
VALUES
  ('Widget'),
  ('Gadget');

SELECT
  id,
  name
FROM
  products
ORDER BY
  id;

Expected result

id	name
1	Widget
2	Gadget

The table-definition syntax is engine-specific, but each insert omits the id and receives generated values.

Useful when

Where this command helps.

creating a table whose primary key is assigned by the database
inserting rows without calculating the next numeric identifier in application code

Explanation

What the command is doing.

Auto-generated numeric IDs are common surrogate keys. Each database has its own table-definition syntax, but the goal is the same: when an insert omits the ID column, the database assigns the next value. This keeps application inserts simple and avoids race-prone manual ID generation.

Generate Row IDs Automatically

Insert products without supplying the id column

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.