AUTO INCREMENT Column Constraint

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