Search code examples
sqlstandards

Standardized way of AUTO_INCREMENT

Is there a standardized way I can create a table in SQL with a column (lets call it ID) that is auto incremental so that I can basically use it in all databases?

(e.g. standardized in SQL-92) If so - how? If not, why? I think auto_increment is a very often used property so I thought it would be very important to standardize it…

Solution

  • Nope, sorry. There is AUTO_INCREMENT in MySQL, but e.g. in MS SQL this is called IDENTITY and SERIAL in PGSQL. Many things are not really standardized in SQL - and most are in the schema creating area.

    It's a mess, but you can use stuff like e.g. Hibernate/NHibernate to try to use a single code base.

    Update: Few year later there is a more standard way that some DBMS support (e.g. PG SQL from version 10.0, so from October 2017):

    • GENERATED BY DEFAULT AS IDENTITY -- the value has a default auto incrementation, but you can insert your own.
    • GENERATED ALWAYS AS IDENTITY -- forbids inserting own values (in a standard query, might be overriden)

    This is something that should work in PG SQL 10+, DB2, Oracle:

    DROP TABLE IF EXISTS simple_test;
CREATE TABLE simple_test(
    s_id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

    Note however that this will not work in Microsoft SQL Server (not even in MS SQL Server 2022). MSSQL does not support the generated keyword. MySQL/MariaDb has generated columns, but MariaDb does not support the identity syntax.

    So yeah, 10 years later the answer is kind of the same really -- it is still a mess and you should probably use a framework for that.