SQL Dialects Reference/Data structure definition/Auto-increment column

A short hint: In most cases auto-increment columns are used as Primary Key columns. In the SQL standard the junction of the two concepts is not mandatory. SQL Standard The SQL standard defines two ways to generate auto-increment values. First, there are identity columns as an extension to exact numeric types. The syntax is: "GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY". Second, the use of sequences in combination with triggers is standardized. CREATE TABLE t1 (col1 DECIMAL GENERATED ALWAYS AS IDENTITY); DB2 Identity columns or sequences combined with triggers (comparison of both techniques). CREATE TABLE t1 (col1 INT GENERATED ALWAYS AS IDENTITY); -- or: CREATE TABLE t1 (col1 INT); CREATE SEQUENCE sequence_name; CREATE TRIGGER insert_trigger NO CASCADE BEFORE INSERT ON t1 REFERENCING NEW AS n FOR EACH ROW SET n.col1 = NEXTVAL FOR sequence_name; Firebird Is recommended to use sequences combined with triggers . From 3.0 there is Identity support. SET TERM ^; CREATE TABLE t1(col1 INTEGER NOT NULL PRIMARY KEY)^ CREATE SEQUENCE sequence_name^ ALTER SEQUENCE sequence_name RESTART WITH 0^ CREATE TRIGGER trigger_name FOR t1 BEFORE INSERT AS BEGIN NEW.col1 = NEXT VALUE FOR sequence_name; END^ Linter AUTOINC columns (maybe with RANGEs) or sequences combined with triggers. CREATE TABLE t1 (col1 SMALLINT AUTOINC); CREATE TABLE t2 (col1 INTEGER AUTOINC); CREATE TABLE t3 (col1 BIGINT AUTOINC); MonetDB CREATE SEQUENCE sequence_name AS INT START WITH 1 INCREMENT BY 1; CREATE TABLE t1 ( col1 BIGINT PRIMARY KEY DEFAULT NEXT VALUE FOR sequence_name, col2 BIGINT AUTO_INCREMENT, col3 BIGINT GENERATED ALWAYS AS IDENTITY ( START WITH 100 INCREMENT BY 2 NO MINVALUE MAXVALUE 1000 CACHE 2 CYCLE) ); CREATE TABLE ts ( col1 SERIAL, /* implies: INTEGER NOT NULL PRIMARY KEY DEFAULT NEXT VALUE FOR "sch"."seq_12345" */ ... ); CREATE TABLE tbs ( col1 BIGSERIAL, /* implies: BIGINT NOT NULL PRIMARY KEY DEFAULT NEXT VALUE FOR "sch"."seq_23456" */ ... ); MSSQL CREATE TABLE t1 (col1 INT IDENTITY(1,1)); MySQL CREATE TABLE t1 (col1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT); OpenLink Virtuoso IDENTITY (start with 1, increment by 1); CREATE TABLE t1 (col1 INTEGER IDENTITY); -- or: CREATE TABLE t1 (col1 INTEGER IDENTITY (start with 1)); Oracle CREATE TABLE t1 (col1 NUMBER PRIMARY KEY); CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1; CREATE OR REPLACE TRIGGER trigger_name BEFORE INSERT ON t1 FOR EACH ROW DECLARE max_id NUMBER; cur_seq NUMBER; BEGIN IF :NEW.col1 IS NULL THEN -- normal assignment of the next value in the sequence :NEW.col1 := sequence_name.NEXTVAL; ELSE -- or allow the user to specify the value, so must advance the sequence to match SELECT GREATEST(COALESCE(MAX(col1), 0), :NEW.col1) INTO max_id FROM t1; WHILE cur_seq < max_id LOOP SELECT sequence_name.NEXTVAL INTO cur_seq FROM DUAL; END LOOP; END IF; END; -- since Oracle 12.1: CREATE TABLE t1 (col1 NUMBER GENERATED BY DEFAULT AS IDENTITY); PostgreSQL create table t1 (col1 serial primary key); -- since postgres 10: create table t1 (col1 integer generated by default as identity primary key); SQLite Both create an autoincrementing column; the AUTOINCREMENT keyword only prevents reusing deleted values. CREATE TABLE t1 (col1 INTEGER PRIMARY KEY); CREATE TABLE t1 (col1 INTEGER PRIMARY KEY AUTOINCREMENT);