Difference between revisions of "AUTO INCREMENT Column Constraint"
Yvonne.milne (Talk | contribs) m (1 revision: SQL) |
Yvonne.milne (Talk | contribs) |
||
| Line 1: | Line 1: | ||
==Purpose== | ==Purpose== | ||
Column constraint to auto increment the value of a column | Column constraint to auto increment the value of a column | ||
| − | |||
==Syntax== | ==Syntax== | ||
AUTO_INCREMENT | AUTO_INCREMENT | ||
| − | |||
==See Also== | ==See Also== | ||
[[ALTER TABLE]], [[SQL Constraints|CONSTRAINTS]], [[CREATE TABLE]] | [[ALTER TABLE]], [[SQL Constraints|CONSTRAINTS]], [[CREATE TABLE]] | ||
| − | |||
==Description== | ==Description== | ||
| Line 17: | Line 14: | ||
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. | 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== | ==Example== | ||
| Line 30: | Line 26: | ||
</code> | </code> | ||
| − | |||
| − | |||
| − | |||
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:SQL]] | [[Category:SQL]] | ||
Latest revision as of 11:11, 26 October 2011
Contents
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
