Difference between revisions of "Autonumber fields"
From SQLZOO
| 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 09: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;
Autonumber fields
