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,
  name VARCHAR(20)
);

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 integer types like:

• TINYINT
• SMALLINT
• INTEGER
• BIGINT

SQLite

Numerical IDs are generated for integer columns which contains the PRIMARY KEY and AUTOINCREMENT options. It should also include "NOT NULL" to be compliant to ANSI SQL.

CREATE TABLE table (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(20)
);

Contrary to other database systems, SQLite only allows the INTEGER type for columns which should generate IDs. It's also not possible to use a primary key spanning multiple columns.