Difference between revisions of "AUTO INCREMENT Column Constraint"
Helengeorge (Talk | contribs) (→Products) |
Yvonnemilne (Talk | contribs) |
||
| Line 21: | Line 21: | ||
==Example== | ==Example== | ||
<code lang="recital"> | <code lang="recital"> | ||
| − | + | CREATE TABLE newcust; | |
| − | CREATE TABLE newcust (acc_num INT AUTO_INCREMENT, acc_name char(20)) | + | (acc_num INT AUTO_INCREMENT, acc_name char(20)) |
| − | INSERT INTO newcust (acc_name) VALUES ("Smith") | + | INSERT INTO newcust; |
| − | INSERT INTO newcust (acc_name) VALUES ("Jones") | + | (acc_name) VALUES ("Smith") |
| − | SELECT * FROM newcust | + | INSERT INTO newcust; |
| + | (acc_name) VALUES ("Jones") | ||
| + | SELECT * FROM newcust | ||
</code> | </code> | ||
Latest revision as of 11:15, 22 December 2009
Purpose
Column constraint to auto increment the value of a column
Syntax
AUTO_INCREMENT
See Also
ALTER TABLE, CONSTRAINTS, CREATE TABLE
Description
A constraint is used to define rules that help to provide data integrity. Column constraints are specific to the column name specified. You must have ALTER privilege on the table. The table will be locked for EXCLUSIVE use during the operation.
The AUTO_INCREMENT column constraint is used to auto increment the value of a column whenever a new record is inserted. The first record to be inserted has a column value of 1 and for each new record the value increments by 1. A column with the AUTO_INCREMENT constraint set is not read only; values can be inserted into the field, but it will default to auto incrementing if no value or a .NULL. is specified.
The AUTO_INCREMENT value increases on a per-table basis, using the SEQNO() function in the DEFAULT constraint of the column. Only one column per table can have the AUTO_INCREMENT constraint set.
Example
CREATE TABLE newcust; (acc_num INT AUTO_INCREMENT, acc_name char(20)) INSERT INTO newcust; (acc_name) VALUES ("Smith") INSERT INTO newcust; (acc_name) VALUES ("Jones") SELECT * FROM newcust
Products
Recital, Recital Server
