Difference between revisions of "AUTOINC Column Constraint"

From Recital Documentation Wiki
Jump to: navigation, search
 
 
(2 intermediate revisions by one user not shown)
Line 1: Line 1:
=AUTOINC=
 
 
 
==Class==
 
Column Constraints
 
 
 
 
==Purpose==
 
==Purpose==
 
Column constraint to auto increment the value of a column
 
Column constraint to auto increment the value of a column
Line 30: Line 23:
 
==Example==
 
==Example==
 
<code lang="recital">
 
<code lang="recital">
set sql to vfp
+
CREATE TABLE newcust;
CREATE TABLE newcust (acc_num INT AUTOINC NEXTVALUE 10 STEP 5, acc_name char(20))
+
  (acc_num INT AUTOINC NEXTVALUE 10 STEP 5, 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")
 +
INSERT INTO newcust;
 +
  (acc_name) VALUES ("Jones")
 
SELECT * FROM newcust
 
SELECT * FROM newcust
 
</code>
 
</code>
Line 39: Line 34:
  
 
==Products==
 
==Products==
Recital Database Server, Recital Mirage Server, Recital Terminal Developer
+
Recital, Recital Server
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:SQL]]
 
[[Category:SQL]]

Latest revision as of 11:17, 22 December 2009

Purpose

Column constraint to auto increment the value of a column


Syntax

AUTOINC [NEXTVALUE <NextValue> [STEP <StepValue>]]


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 AUTOINC column constraint is used to auto increment the value of a column whenever a new record is inserted. The NEXTVALUE <NextValue> clause may optionally be used to set the column value of the next inserted record to the integer <NextValue>. The optional STEP <StepValue> clause determines the integer amount the value should be incremented each time a new record is inserted. By default, the first record to be inserted has a column value of 1 and for each new record the value increments by 1.

Specifying the AUTOINC column constraint causes the column to be read only.

NOTE: AUTOINC is column based and multiple columns in the same table can have the AUTOINC column constraint set. The Recital SEQNO() function works on a per table basis.


Example

CREATE TABLE newcust;
  (acc_num INT AUTOINC NEXTVALUE 10 STEP 5, 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