🎓 A1Learner
🔍

SQL Auto-Increment Keys

An auto-increment primary key fills itself when an insert leaves the key out. SQLite gives this behaviour to every INTEGER PRIMARY KEY column.

One row, no key given

The insert names only the note column. The select shows that note_id still holds a value, and the value is 1.

CREATE TABLE notes (
  note_id INTEGER PRIMARY KEY,
  note TEXT
);
INSERT INTO notes (note) VALUES ('Restock pencils');
SELECT note_id, note
FROM notes;

The numbers climb

Two inserts leave reminder_id out. The keys come back as 1 and 2, in insert order.

CREATE TABLE reminders (
  reminder_id INTEGER PRIMARY KEY,
  reminder TEXT
);
INSERT INTO reminders (reminder) VALUES ('Water the plants');
INSERT INTO reminders (reminder) VALUES ('Lock the shop');
SELECT reminder_id, reminder
FROM reminders
ORDER BY reminder_id;

Example

CREATE TABLE notes (
  note_id INTEGER PRIMARY KEY,
  note TEXT
);
INSERT INTO notes (note) VALUES ('Call the supplier');
INSERT INTO notes (note) VALUES ('Check lamp stock');
INSERT INTO notes (note) VALUES ('Order more mugs');
SELECT note_id, note
FROM notes
ORDER BY note_id;

Key points

  • Use a column list in INSERT to leave the key column out.
  • The first filled value is 1.
  • Each new row gets the next number.
  • An INTEGER PRIMARY KEY column fills itself in this way.

In this example, CREATE TABLE builds notes with an INTEGER PRIMARY KEY. Each insert names only the note column and leaves note_id out. The engine fills note_id itself, starting at 1 and climbing by one per row. The select shows the three filled keys, 1, 2 and 3.

Note

MySQL writes AUTO_INCREMENT after the column type for this behaviour. Other engines write IDENTITY.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What must an insert do to let the key fill itself?