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

Revision as of 21:49, 31 August 2007

Firebird

The Firebird and Interbase database server are using generators to create incremental IDs, which are assigned by triggers to a table column. The approach is a little bit more complex than the other ones but is rather flexible.

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

CREATE GENERATOR gen_table_id;

SET TERM !!;
CREATE TRIGGER trig_table FOR table
ACTIVE BEFORE INSERT AS
BEGIN
	IF (NEW.id IS NULL) THEN
	NEW.id = GEN_ID(gen_table_id,1);
END !!
SET TERM ;!!

MS SQL Server / Sybase ASE

The Microsoft SQL server used the same method as it's parent, the Sybase server. It's a similar method to MySQLs simple auto increment but you are able to specify more options how the ID's should be generated.

CREATE TABLE table (
  id INTEGER NOT NULL IDENTITY,
  name VARCHAR(20)
);

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

Oracle

A combination of a sequence and a trigger is the way an Oracle database generates IDs for newly inserted records. It's slightly more complex and not as flexible than the approach used by PostgreSQL.

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

CREATE SEQUENCE "seq_test_id" START WITH 1 INCREMENT BY 1;

CREATE TRIGGER trig_table_id
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
	SELECT seq_table_id.nextval INTO :new.id FROM dual;
END;

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 table (
  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.