Eine Einführung in
SQL
CREATE a table with an autonumber / sequence / identity / autoincrement
SQLite
An auto number field can provide a unique identifier where no other is available. These should be avoided if at all possible.
Execute tidy up
code
silently
DROP TABLE t_test; DROP sequence sq
CREATE TABLE t_test( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(10) ); INSERT INTO t_test(name) VALUES ('Andrew'); INSERT INTO t_test(name) VALUES ('Gordon'); SELECT * FROM t_test;
Specific to SQLite
none
DB2
An auto number field can provide a unique identifier where no other is available. These should be avoided if at all possible.
Execute tidy up
code
silently
DROP TABLE t_test; DROP sequence sq
CREATE TABLE t_test( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(10) ); INSERT INTO t_test(name) VALUES ('Andrew'); INSERT INTO t_test(name) VALUES ('Gordon'); SELECT * FROM t_test;
Specific to DB2
CREATE TABLE
MS Access
An auto number field can provide a unique identifier where no other is available. These should be avoided if at all possible.
Execute tidy up
code
silently
DROP TABLE t_test; DROP sequence sq
CREATE TABLE t_test( id COUNTER PRIMARY KEY, name VARCHAR(10) ); INSERT INTO t_test(name) VALUES ('Andrew'); INSERT INTO t_test(name) VALUES ('Gordon'); SELECT * FROM t_test;
Specific to MS Access
Use
COUNTER
PostgreSQL
An auto number field can provide a unique identifier where no other is available. These should be avoided if at all possible.
Execute tidy up
code
silently
DROP TABLE t_test; DROP sequence sq
CREATE SEQUENCE sq; CREATE TABLE t_test( id INTEGER PRIMARY KEY DEFAULT NEXTVAL('sq'), name VARCHAR(10) ); INSERT INTO t_test(name) VALUES ('Andrew'); INSERT INTO t_test(name) VALUES ('Gordon'); SELECT * FROM t_test;
Specific to PostgreSQL
none
Oracle
An auto number field can provide a unique identifier where no other is available. These should be avoided if at all possible.
Execute tidy up
code
silently
DROP TABLE t_test; DROP sequence sq
CREATE SEQUENCE sq; CREATE TABLE t_test( id INTEGER PRIMARY KEY, name VARCHAR(10) ); INSERT INTO t_test(id,name) VALUES (sq.nextval,'Andrew'); INSERT INTO t_test(id,name) VALUES (sq.nextval,'Gordon'); SELECT * FROM t_test;
Specific to Oracle
SEQUENCE nextval
MS SQL Server
An auto number field can provide a unique identifier where no other is available. These should be avoided if at all possible.
Execute tidy up
code
silently
DROP TABLE t_test; DROP sequence sq
CREATE TABLE t_test( id INTEGER IDENTITY PRIMARY KEY, name VARCHAR(10) ); INSERT INTO t_test(name) VALUES ('Andrew'); INSERT INTO t_test(name) VALUES ('Gordon'); SELECT * FROM t_test;
Specific to MS SQL Server
IDENTITY property
MySQL
An auto number field can provide a unique identifier where no other is available. These should be avoided if at all possible.
Execute tidy up
code
silently
DROP TABLE t_test; DROP sequence sq
CREATE TABLE t_test( id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10) ); INSERT INTO t_test(name) VALUES ('Andrew'); INSERT INTO t_test(name) VALUES ('Gordon'); SELECT * FROM t_test;
Specific to MySQL
The may be only one AUTO_INCREMENT field per table. That field must be the primary key.
CREATE TABLE
Sybase
An auto number field can provide a unique identifier where no other is available. These should be avoided if at all possible.
Execute tidy up
code
silently
DROP TABLE t_test; DROP sequence sq
CREATE SEQUENCE sq; CREATE TABLE t_test( id INTEGER PRIMARY KEY DEFAULT NEXT_VALUE OF sq, name VARCHAR(10) ); INSERT INTO t_test(name) VALUES ('Andrew'); INSERT INTO t_test(name) VALUES ('Gordon'); SELECT * FROM t_test;
Specific to Sybase
none
Mimer SQL
An auto number field can provide a unique identifier where no other is available. These should be avoided if at all possible.
Execute tidy up
code
silently
DROP TABLE t_test; DROP sequence sq
CREATE SEQUENCE sq; CREATE TABLE t_test( id INTEGER PRIMARY KEY DEFAULT NEXT_VALUE OF sq, name VARCHAR(10) ); INSERT INTO t_test(name) VALUES ('Andrew'); INSERT INTO t_test(name) VALUES ('Gordon'); SELECT * FROM t_test;
Specific to Mimer SQL
We create a
SEQUENCE
and ensure that the autonumber field defaults top the next value.