Portable SQL/Statements/IDs/Generating IDs

Standard SQL

In SQL 2003 generating (unique) IDs was finally standardized. The standard defines two possible ways to create IDs which varies in flexibility and ease of use. The first one is a very flexible sequence based approach:

CREATE SEQUENCE <sequence name> [AS ]
 [START WITH <num>] [INCREMENT BY <num>]
 [MINVALUE <num>] [MAXVALUE <num>]
 [CYCLE | NO CYCLE]

You can define several sequences with different constraints and use them in one or more tables. The drawback is that the sequence must be mentioned in every INSERT statement because it can not be assigned as default value to a column. The data type can be every integer or numeric type with a scale of zero (this means a number without fractions).

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

CREATE SEQUENCE seq_table_id

INSERT INTO table (id, name) 
VALUES (NEXT VALUE FOR seq_table_id, 'a name')

Alternatively, it's possible to use the column based IDENTITY concept which accepts the same options as the sequence:

<identity column> :=
  <column name> <data type> 
  GENERATED [ALWAYS|BY DEFAULT] AS IDENTITY
  [START WITH <num>] [INCREMENT BY <num>]
  [MINVALUE <num>] [MAXVALUE <num>]
  [CYCLE | NO CYCLE]

CREATE TABLE table ( 
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR(20)
)

Firebird / Interbase

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 or 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)
);

There's a special data type called SERIAL (or BIGSERIAL for 64bit integers) which simplifies but also limits the possibility a little bit. It creates a sequence and attaches the values automatically to the column.

CREATE TABLE table (
  id SERIAL,
  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 (SMALLINT, INTEGER, BIGINT) are allowed for sequences.

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.

Conclusion

There's a strong diversity between the database implementations because generating ID was standardized very late. Also, database vendors doesn't seem to catch up with SQL 2003 quickly and so developers will struggle further years with different methods.

It's impossible to use a single statement for all databases but there is one other important part to allow portability: Don't use all the advanced features one implementation provides. This would lead to a application which will never be able to run one a different database.

Instead, there are four rules which you can follow:

• Don't share generated IDs between tables
• Use only one column per table with generated IDs
• Assign a PRIMARY KEY to this column and only this column
• Specify NOT NULL for this column

It's not necessary to always use INTEGER as column type because the CREATE TABLE statement is specific for the database nevertheless. You can use the appropriate data type instead which fits best for your needs. If you need e.g. BIGINT and a database doesn't support such big numbers, your customers should be aware that it works but this has limits.