What is the difference between AUTO_INCREMENT, IDENTITY and a standalone sequence?
They all give you a number that nobody has to think about, and they differ in where that number comes from.
AUTO_INCREMENT a property of the column (MySQL, SQLite)
IDENTITY a property of the column (the standard, PostgreSQL, SQL Server)
SEQUENCE a separate object that you draw numbers from (Oracle, PostgreSQL)
So what is the difference between the first two and the third? An identity column and an auto increment column make their own numbers. A sequence is a thing of its own, sitting in the database like a table, and you ask it for the next number.
-- The MySQL and SQLite spelling.
CREATE TABLE tickets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title VARCHAR(160) NOT NULL
);
-- The standard spelling, which PostgreSQL and SQL Server use.
CREATE TABLE tickets (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title VARCHAR(160) NOT NULL
);
There are two differences that matter in practice. The first one is that a sequence is shared. Two tables can both draw from the same sequence, and so every number in the database is different, which is useful when the numbers have to be unique across the whole system. The second one is that a sequence gives you control over the numbers. You can ask for a number and not use it, you can set it to a high value so that the new numbers are above the old ones, and you can restart it.
The next question in this topic asks for a table that does it automatically.