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;