AUTONUM (Short name: AUT)
Top  Previous  Next

Use the AUTONUM command to define, modify, or remove an autonumber formula from a column.

autonum

Options

colname
Specifies a 1 to 18 character column name. In a command, you can enter #c, where #c is the column number shown when the columns are listed with the LIST TABLES command.

DELETE
Removes a column's autonumber formula.

format
Defines the format in which values are displayed. This option is used only for columns with the TEXT data type.
You can use the following formatting characters:

Formatting Character
Result


9
Specify a numeric digit; leading zeros are suppressed.
0
Specify a numeric digit; leading zeros are displayed.
. (period)   
Aligns digits along a decimal point.
[ ] (square brackets)
Encloses literal text.

      
For example, if the format is [MX]9999 and the numeric value is 123, the value entered will be MX123.

IN tblname
Specifies the table in which to autonumber the column.

increment
Specifies the value of the increment as each new row is added to the table. The default increment is 1.

NONUM
Leaves existing values unchanged and assigns autonumbered values to new rows as they are added to the table. NONUM is the default option.

NUM
Renumbers all the existing values in the column defined as an autonumbered column.

ORDER BY clause
Sorts rows of data. The ORDER BY clause is only used with the NUM option. For more information, see the "ORDER BY" entry.

USING startnum
Defines or redefines the formula for an autonumber column. You must specify a starting value. Optionally, you can specify an increment, and for columns with the TEXT data type, a display format. For a column in a table that contains values, you can either renumber existing values or leave them as they are.

About the AUTONUM Command

An autonumbered column ensures that each row in that column has an incremental value. For example, use an autonumbered column to assign identification numbers, model numbers, or invoice numbers:

The following types of columns can be autonumbered:

·Columns that are not computed.  
·Columns with DOUBLE, INTEGER, NUMERIC, REAL, or TEXT data types.  

When you use the LIST command to list information about a column or table, autonumbered columns are described as AUTONUMBER in the attributes column.

Automatic Numbering

R:BASE automatically enters values in an autonumbered column when you add rows to a table using a form, the Data Editor, INSERT command, or LOAD command with the NUM option. When you import rows to a table that contains an autonumber column, you can either set autonumbering off and load imported values, or set autonumbering on and let R:BASE autonumber the values.

Changing Values

You can change the values in an autonumbered column by using a form, or the UPDATE or EDIT command. However, if you change a value in an autonumbered column, you could assign a duplicate number or disrupt the sequence of numbers. For more information about changing values in an autonumbered column, see the guidelines in "Renumbering Columns Containing Data" later in this entry.

Renumbering Columns Containing Data

If you renumber a column that contains data, use the following guidelines to decide whether to change the column's existing values.

Autonumber?
Option to Use
Conditions



Yes
NUM
A column exists in only one table in the database. You can use the ORDER BY NUM clause to sort the rows in the order in which you want them renumbered. When you add new rows, values are numbered in the order in which the rows are added to the table.
No
NONUM
(or do not specify)
A linking (or common) column exists in more than one table. You will destroy the common column values that link your tables if you renumber the values in a linking column. R:BASE adds autonumbered values to new rows as you add them to the table.


Redefining Formulas

You can redefine the formula for an autonumbered column. For example, use the AUTONUM command with the NUM option to change a column's display format from suppressing leading zeros to displaying them. For more information about redefining formulas of an autonumbered column, see "Renumbering Columns" earlier in this entry.

Removing Formulas

To remove an autonumber formula for a column, use the DELETE option. R:BASE removes only the formula, not the existing values in the autonumbered column. After you remove an autonumber formula, the user must enter values in the column as rows are added.

Autonumbering Tables Created with Relational Commands

When you create a table with one of the relational commands (INTERSECT, JOIN, PROJECT, SUBTRACT, or UNION), R:BASE transfers an autonumbered column as a regular column. You must define an autonumber formula for the column in the new table.

Database Access Rights with AUTONUM

When access rights for a table have been assigned using the GRANT command, AUTONUM requires either the database owner's user identifier, or the rights to alter a table.

Examples

The following command defines an autonumber formula for the custid column in the customer table. Existing values are renumbered starting at 100; assigned values increase by one for each row. Only use this command for a column that meets the renumbering guidelines in the section "Renumbering Columns Containing Data."

AUTONUM custid IN customer USING 100 1 NUM

The following command defines an autonumber formula for the model column in the product table. Existing values are not renumbered. Values in new rows are numbered starting with 100. Assigned values increase by one each time a row is added. The numbering format specifies that the letters MX always precede the numeric value. The 0000 provides space for a numeric value of up to four digits. When the value is less than four digits, R:BASE enters leading zeros.

AUTONUM model IN product USING 100 1 [MX]0000 NONUM

The following command assigns autonumbering to the empid column in the employee table. Existing values are renumbered starting at 100; assigned values increase by one for each row. The rows are renumbered by the employees' last and first names. Only use this command for a column that meets the renumbering guidelines in the section "Renumbering Columns Containing Data."

AUTONUM empid IN employee USING 100 1 ORDER BY emplname, empfname NUM

The command below deletes the autonumber formula from the empidcolumn in the employee table.

AUTONUM empid IN employee DELETE