Can my DB?

-- Preferred: SQL standard identity column
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL
);

-- Allow explicit inserts with OVERRIDING SYSTEM VALUE
INSERT INTO users (id, email)
OVERRIDING SYSTEM VALUE
VALUES (999, 'admin@example.com');

-- Older shorthand (still supported)
CREATE TABLE users_old (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL
);

• Prefer GENERATED AS IDENTITY for new code; SERIAL / BIGSERIAL still works but is the older style.
• GENERATED ALWAYS requires OVERRIDING SYSTEM VALUE to insert an explicit id; GENERATED BY DEFAULT accepts one without ceremony.
• ALTER TABLE ... ALTER COLUMN ... RESTART changes the underlying sequence.

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

-- Retrieve last inserted auto-increment value
SELECT LAST_INSERT_ID();

• One AUTO_INCREMENT column per table, and it must be part of a key.
• Reset with ALTER TABLE ... AUTO_INCREMENT = n. LAST_INSERT_ID() returns the session's most recent value.

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

SELECT LAST_INSERT_ID();

• AUTO_INCREMENT for the common case; for more flexibility, 10.3+ has CREATE SEQUENCE and NEXTVAL(seq) as a column default.

-- Always generated (no manual inserts)
CREATE TABLE users (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR2(255) NOT NULL
);

-- Allow manual inserts
CREATE TABLE orders (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY
    START WITH 1 INCREMENT BY 1 CACHE 20,
  total NUMBER
);

• Added in 12c. Third mode: GENERATED BY DEFAULT ON NULL, which only generates a value when the insert passes NULL.
• Backed by a sequence object under the hood.

CREATE TABLE users (
  id INT IDENTITY(1, 1) PRIMARY KEY,
  email NVARCHAR(255) NOT NULL
);

-- Allow explicit inserts temporarily
SET IDENTITY_INSERT dbo.users ON;
INSERT INTO dbo.users (id, email) VALUES (999, 'admin@example.com');
SET IDENTITY_INSERT dbo.users OFF;

-- Retrieve last inserted identity value
SELECT SCOPE_IDENTITY();

• Syntax is IDENTITY(seed, increment), not GENERATED AS IDENTITY.
• SET IDENTITY_INSERT tbl ON temporarily allows explicit ids; SCOPE_IDENTITY() returns the last inserted id in scope.
• For more flexibility, CREATE SEQUENCE is available since 2012.

-- Any INTEGER PRIMARY KEY column auto-increments
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);

-- AUTOINCREMENT prevents rowid reuse (rarely needed)
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  total REAL
);

• Any INTEGER PRIMARY KEY column becomes an alias for the internal rowid and auto-increments on insert.
• AUTOINCREMENT additionally prevents rowid reuse after the max is reached — small perf cost, rarely needed.
• LAST_INSERT_ROWID() returns the rowid of the most recent successful INSERT.

-- SQL standard syntax
CREATE TABLE users (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

-- Legacy IDENTITY syntax
CREATE TABLE orders (
  id BIGINT IDENTITY(1, 1),
  total DECIMAL(10, 2)
);

• Accepts both GENERATED [ALWAYS | BY DEFAULT] AS IDENTITY and the legacy IDENTITY(seed, step).
• Values aren't strictly sequential — expect gaps from the distributed architecture.

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY
    (START WITH 1 INCREMENT BY 1 CACHE 20),
  email VARCHAR(255) NOT NULL
);

• Early implementation of GENERATED AS IDENTITY; backed by a sequence visible in the catalog.
• Options: CYCLE, MINVALUE, MAXVALUE, CACHE, ORDER.

CREATE TABLE users (
  id INT AUTOINCREMENT PRIMARY KEY,
  email STRING NOT NULL
);

-- Equivalent IDENTITY syntax
CREATE TABLE orders (
  id INT IDENTITY(1, 1),
  total FLOAT
);

• Spelling is AUTOINCREMENT or IDENTITY(start, step), not GENERATED AS IDENTITY.
• Values aren't strictly sequential under concurrency — expect gaps.

• Not supported. GENERATE_UUID() covers unique string ids; otherwise, generate ids upstream.