Difference between revisions of "Autonumber fields"

From SQLZOO
Jump to: navigation, search
 
Line 98: Line 98:
  
 
[http://dev.mysql.com/doc/mysql/en/create-table.html CREATE TABLE]
 
[http://dev.mysql.com/doc/mysql/en/create-table.html CREATE TABLE]
[http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html  
+
[http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Examples of auto increment]</div>
Examples of auto increment]</div>
+
 
<div class="ecomm e-db2" style="display: none">[https://aurora.vcu.edu/db2help/db2s0/frame3.htm#sqls0621 CREATE TABLE]</div>
 
<div class="ecomm e-db2" style="display: none">[https://aurora.vcu.edu/db2help/db2s0/frame3.htm#sqls0621 CREATE TABLE]</div>
 
<div class="ecomm e-sqlserver" style="display: none">[http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_3iex.asp IDENTITY property]</div>
 
<div class="ecomm e-sqlserver" style="display: none">[http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_3iex.asp IDENTITY property]</div>

Latest revision as of 10:38, 18 July 2012

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