Difference between revisions of "Portable SQL/Statements/IDs/Generating IDs"

Revision as of 22:13, 29 August 2007

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

PostgreSQL

Contrary to other databases, PostgreSQL has a concept of sequences which can be attached to a table. Also, there is no need for the column to be a primary or unique key.

CREATE SEQUENCE table_id_seq;

CREATE TABLE tablename (
  id <type> NOT NULL DEFAULT nextval('table_id_seq'),
  name VARCHAR(20)
);

The advantage of sequences is that more than one can be part of a table definition and moreover, they can even be shared across tables.

CREATE SEQUENCE table_id1_seq;
CREATE SEQUENCE table_id2_seq;

CREATE TABLE table (
  id1 <type> NOT NULL DEFAULT nextval('table_id1_seq'),
  id2 <type> NOT NULL DEFAULT nextval('table_id2_seq'),
  name VARCHAR(20)
);

Like in MySQL, all integer types are allowed for sequences:

• 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.