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;

Use COUNTER

We create a SEQUENCE and ensure that the autonumber field

defaults top the next value.

CREATE and DROP Operations

CREATE a new table

DROP a table

Composite primary key

CREATE a foreign key

CREATE a VIEW

Autonumber fields

ALTER TABLE ... ADD COLUMN

ALTER TABLE ... DROP COLUMN