Difference between revisions of "Autonumber fields"

From SQLZOO
Jump to: navigation, search
(Created page with "CREATE a table with an autonumber / sequence / identity / autoincrement <div class='ht'> <div class=params>schema:scott</div> <div> An auto number field can provide a unique i...")
 
Line 97: Line 97:
 
index.
 
index.
  
<a href="http://dev.mysql.com/doc/mysql/en/create-table.html">CREATE TABLE</a><a href=" http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html">
+
[http://dev.mysql.com/doc/mysql/en/create-table.html CREATE TABLE]
Examples of auto increment</a></div>
+
[http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html  
<div class="ecomm e-db2" style="display: none"><a href="https://aurora.vcu.edu/db2help/db2s0/frame3.htm#sqls0621">CREATE TABLE</a></div>
+
Examples of auto increment]</div>
<div class="ecomm e-sqlserver" style="display: none"><a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_3iex.asp">IDENTITY property</a></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-mimer" style="display: none">We create a <code>SEQUENCE</code> and ensure that the autonumber field
 
<div class="ecomm e-mimer" style="display: none">We create a <code>SEQUENCE</code> and ensure that the autonumber field
 
defaults top the next value.
 
defaults top the next value.
 
</div>
 
</div>
<div class="ecomm e-oracle" style="display: none"><a href="http://sqlzoo.napier.ac.uk/big/B/s/a/sql_elements6a.htm">SEQUENCE nextval</a></div>
+
<div class="ecomm e-oracle" style="display: none">[http://sqlzoo.napier.ac.uk/big/B/s/a/sql_elements6a.htm SEQUENCE nextval]</div>
  
 
</div>
 
</div>
 
{{CREATE and DROP ref}}
 
{{CREATE and DROP ref}}

Revision as of 10:36, 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