Autonumber fields - Revision history

Marek at 09:38, 18 July 2012

2012-07-18T09:38:50Z

Marek at 09:36, 18 July 2012

2012-07-18T09:36:20Z

Marek: Created page with "CREATE a table with an autonumber / sequence / identity / autoincrement

schema:scott

An auto number field can provide a unique i..."

2012-07-12T15:29:39Z

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..."

New page

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 identifier where no other is
available.
</div>

<source lang=sql class='tidy'>DROP TABLE t_test;
DROP sequence sq</source>
<source lang=sql class='setup'></source>

<source lang='sql' class='def e-access'>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;
</source>

<source lang='sql' class='def e-db2'>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;
</source>

<source lang='sql' class='def e-mysql'>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;
</source>

<source lang='sql' class='def e-sqlite'>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;
</source>

<source lang='sql' class='def e-sqlserver e-sybase'>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;
</source>

<source lang='sql' class='def e-oracle'>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;
</source>

<source lang='sql' class='def e-postgres'>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;
</source>

<source lang='sql' class='def'>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;
</source>

<div class="ecomm e-access" style="display: none">Use <code>COUNTER</code></div>
<div class="ecomm e-mysql" style="display: none">You may specify AUTO_INCREMENT on a secondary column in a multiple column
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">
Examples of auto increment</a></div>
<div class="ecomm e-db2" style="display: none"><a href="https://aurora.vcu.edu/db2help/db2s0/frame3.htm#sqls0621">CREATE TABLE</a></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-mimer" style="display: none">We create a <code>SEQUENCE</code> and ensure that the autonumber field
defaults top the next value.
</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>
{{CREATE and DROP ref}}

@@ Line 98: / Line 98: @@
 [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-sqlserver" style="display: none">[http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_3iex.asp IDENTITY property]</div>

@@ Line 97: / Line 97: @@
 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-mimer" style="display: none">We create a <code>SEQUENCE</code> and ensure that the autonumber field
 defaults top the next value.
 </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>
 {{CREATE and DROP ref}}

Autonumber fields - Revision history

Marek at 09:38, 18 July 2012

Marek at 09:36, 18 July 2012

Marek: Created page with "CREATE a table with an autonumber / sequence / identity / autoincrement schema:scott An auto number field can provide a unique i..."

Marek: Created page with "CREATE a table with an autonumber / sequence / identity / autoincrement

schema:scott

An auto number field can provide a unique i..."