Autonumber fields

CREATE a table with an autonumber / sequence / identity / autoincrement

schema:scott

An auto number field can provide a unique identifier where no other is available.

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;

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;

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;

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;

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;

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;

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;

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;