SQL Database Design » 9. Default Values » 9.3 Auto-Increment Numbers

9.3 Auto-Increment Numbers

Auto-increment numbers provide a more or less algorithmic approach to generate unique, incremental numbers (unique auto IDs). The SQL:2003 way of specifying auto-increment numbers is via the IDENTITY keyword. Previous standards did not have it though. IDENTITY can only be used on integral types. According to the SQL standard, the IDENTITY keyword must be used with another SQL:2003 keyword: GENERATED. The syntax is as follows:

GENERATED [ ALWAYS | BY DEFAULT ] AS IDENTITY
  [ START WITH start_value INCREMENT BY increment ]

Note, this is not the full syntax. An identity column also has a start value, an increment, a maximum value, a minimum value, and a cycle option. The increment can also be negative. The minimum and maximum values as well as the cycle option are not mandatory. The latter is not applicable for primary key columns, of course (no duplicates allowed). ALWAYS must be used for derived values at all times. BY DEFAULT can only be used in conjunction with IDENTITY.

I think the syntax does not require any further explanation, other than the ALWAYS and BY DEFAULT parts. The difference between these is, that for ALWAYS the column's value is always set no matter what, while BY DEFAULT only performs conditionally, that is, if a value was not specified. (The latter behavior should also apply for any FOREIGN KEY ... SET DEFAULT constraints that fail.)

Only some of the more recent DBMSs support the IDENTITY keyword: PostgreSQL introduced it with version 8.4, Oracle has it since version 10, and SQL Server has it since the 2000 version. (DB2 and Sybase also seem to support the IDENTITY keyword). MySQL does not support IDENTITY, but offers the proprietary keyword AUTO_INCREMENT instead. You might not be able to avoid it when working with MySQL. You can have only one AUTO_INCREMENT column per table and it must be part of a (primary) key. By default, the AUTO_INCREMENT keyword starts counting at one, which can be overriden, but it always increments by one. These defaults are also used by most IDENTITY implementations.

Usually, algorithms to determine the next vacant ID are constructed so that unique auto-increment numbers do not look for gaps, that is, any ID that has previously been generated is not generated again. As such, inserting a row into a table and deleting that directly thereafter will not free the just generated number. There are data integrity reasons for this. Note, that there have been bugs in some DBMSs that allowed a server reset to forget about the already generated numbers.

PostgreSQL and MySQL offer a SERIAL type, Oracle and SQL Server do not. SERIAL is not standard SQL. In MySQL a SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, in PostgreSQL it has the same effect as this:

CREATE SEQUENCE tablename_colname_seq

CREATE TABLE tablename
(
    colname INTEGER NOT NULL DEFAULT nextval('tablename_colname_seq')
)

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname

Refer to the sequences section for information on how they work. Because of the SERIAL implementation inconsistencies, I do not recommend to use it. As for IDENTITY the following DB2 source writes this:

... IDENTITY columns .. are difficult to manage and modify. Once you have made the decision to use GENERATED BY DEFAULT, you can't easily change to GENERATED ALWAYS, and you can't do it using ALTER.

One approach would be to DROP the table and recreate it the way you want it to be. But then you have to worry about how to get the values back that are already there. This might be a good time to think about switching to Sequence Objects.

Simple defaults, IDENTITY, the (proprietary) AUTO_INCREMENT, and SERIAL can only be specified inline in a CREATE TABLE statement.

Last updated: 2010-08-04