Autonumber fields

From SQLZOO
Revision as of 16:29, 12 July 2012 by Marek (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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;
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense