Standardized auto_increment mode

Question

Is there a standardized way to create a table in SQL with a column (allows you to call its ID) that is automatically incremental so that I can mainly use it in all databases?

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

Answer 1

No, sorry. There is AUTO_INCREMENT in MySQL, but, for example, in MS SQL this is called IDENTITY. Many things are not standardized in SQL - and most of them are in the area of ​​schema creation.

This is a mess, but you can use things like for example. Hibernate / NHibernate to try to use a single code base.

Answer 2

In Oracle, you need to create a SEQUENCE

SQLite uses it for rowid and its synonym, for example. RowIdSyn INTEGER PRIMARY KEY AUTOINCREMENT

Answer 3

You can use the so-called identification columns:

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

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

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