Portable SQL/Statements/IDs/Generating IDs

MySQL

A table can have only one column of an integer type for which an ID is generated. It must be part of a PRIMARY or UNIQUE key and "NOT NULL" should be part of the column definition.

CREATE TABLE table (
  id <type> NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE table (
  id <type> NOT NULL AUTO_INCREMENT,
  name VARCHAR(20),
CONSTRAINT unq_table_id_name
  UNIQUE KEY (id,name)
);

Allowed column types are only integer types like:

• TINYINT
• SMALLINT
• INTEGER
• BIGINT

SQLite

CREATE TABLE table (
  id INTEGER NOT NULL AUTOINCREMENT
);

Only INTEGER column types are allowed for generated IDs.