Difference between revisions of "AUTO INCREMENT Column Constraint"

From Recital Documentation Wiki
Jump to: navigation, search
(Products)
 
Line 21: Line 21:
 
==Example==
 
==Example==
 
<code lang="recital">
 
<code lang="recital">
set sql to mysql
+
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