8

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…

Michael Mior
  • 28,107
  • 9
  • 89
  • 113
Novellizator
  • 13,633
  • 9
  • 43
  • 65
  • http://www.w3schools.com/sql/sql_autoincrement.asp my-sql is auto increment . sql server is identity, access is AUTOINCREMENT – Gilad Feb 28 '13 at 23:27
  • 1
    Unfortunately, there is no standard for auto-increment. Oracle for example offers sequences only. You can create a `BEFORE INSERT` trigger to simulate auto-increment behaviour however there is no native implementation. [PostgreSQL is different yet again](http://stackoverflow.com/questions/787722/postgresql-autoincrement) – Phil Feb 28 '13 at 23:29

3 Answers3

2

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.

Nux
  • 9,276
  • 5
  • 59
  • 72
1

You can use so-called identity columns:

CREATE TABLE foo(id int GENERATED ALWAYS AS IDENTITY);

This is in the SQL standard and should be supported by PostgreSQL 10 and Oracle:

https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/#comment-248607

Marko Knöbl
  • 447
  • 2
  • 9
  • I think this is the correct answer as far as standards go. Unfortunately, with SQL, databases are generally not fully standards compliant. – Michael Mior Jan 20 '23 at 14:15
  • PG10 is from Oct 2017. That is 4.5 years after this question ;-). And it is still not supported in MariaDB/MySQL, so not a replacement for `AUTO_INCREMENT` in any way. – Nux Jan 20 '23 at 22:53
-1

In Oracle you need to create a SEQUENCE

SQLite uses it for rowid and a synonym of it e.g. RowIdSyn INTEGER PRIMARY KEY AUTOINCREMENT

Klaas-Z4us-V
  • 187
  • 1
  • 5