standardized way of auto_increment

Question

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 basicly 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 is very important to standardize it...

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
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

score 2 · Accepted Answer · answered Feb 28 '13 at 23:30

Nope, sorry. There is AUTO_INCREMENT in MySQL, but e.g. in MS SQL this is called IDENTITY. 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.

score 1 · Answer 2 · answered Apr 27 '19 at 09:37

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

score 0 · Answer 3 · answered Nov 25 '17 at 21:42

0

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

answered Nov 25 '17 at 21:42

Klaas-Z4us-V

141
1
4

standardized way of auto_increment

3 Answers3