Is there an ANSI standard for auto-numbered columns.
Currently, we have a choice of SERIAL
, AUTOINCREMENT
, AUTO_INCREMENT
, IDENTITY()
and good old NEXTVAL()
among others.
I read somewhere that there is a new standard using IDENTITY
which I know that Oracle has recently implemented. I know that Oracle is hardly the last word on standards.
If there is a standard, then it’s a long time coming.
Best Answer
Yes there is, and it's definitely not "a new standard". It's been in the SQL standard since SQL:2003
The definition is:
The optional part with the sequence generator options lets you define the characteristics of the underlying sequence (=generator), you could do something like:
This syntax is supported (at least) by PostgreSQL 10, Oracle 12.1, DB2, Apache Derby, HSQLDB, Firebird and NuoDB.