AX2648

About Identity columns

Columns that use the Identity data type contain automatically-generated, unique ID numbers. The primary use for this column type is to support the option to add plan files "on demand" for a file group. For example, when a user selects to create a plan file for an on-demand file group, a new record is added to the plan code table and a unique ID number is automatically generated. However, identity columns can be used for other purposes as needed.

The ID numbers for identity columns start at 1 and increment using whole numbers. You cannot specify a different starting value for the identity column, or any other ID format.

NOTE: The automatically-generated ID numbers are not necessarily sequential. There are many conditions that may cause numbers to be "skipped," such as encountering an error when attempting to create a new plan file, or restarting the SQL service. Skipped numbers should be expected during normal system use and do not indicate any issue with your system.

There are two different data types for identity columns, regular Identity and Identity 32. Identity 32 takes up less space in the database and is recommended to be used unless the regular Identity is required for lookup purposes. Both data types behave the same way from a feature perspective.

Column linking and identity columns

Identity columns cannot have assigned lookup columns. Because the numbers in the identity column are automatically-generated, they cannot be validated against another list.

However, identity columns can serve as the lookup column for columns in other tables, if you want to limit the other column to only using values from the identity column. The column linked to the identity column must use the Integer data type, and it must match the size of the Identity column. For example, a regular Integer column can look up to a regular Identity column, and an Integer 32 column can look up to an Identity 32 column, but a regular Integer column cannot look up to an Identity 32 column.

Creating new records in identity tables

There are a variety of ways to add new records to a table with an identity key column. The following table summarizes these features:

Process	Behavior
Add Plan File	On-demand file groups use this process to automatically generate a new record in the plan code table and create a plan file. This functionality can be triggered using the built-in action in the Open Plan Files dialog, or by using the Add Plan File command.
Add Identity Record	This command can be used in Axiom forms to create a new record in a target identity table.
Save Type 1	To create new records, leave the identity key column value blank and disable aggregation for the save. For each row in which the identity column is left blank, a new record is added to the table with an automatically generated ID value. Alternatively, it is also possible to populate the identity key column with specific values that do not already exist in the table. When the save occurs, new records are created using those specific values. However, note that the identity "seed" value is reset to the largest inserted value (if that value is greater than the previous seed value). For example, if the last auto-generated value was 20, but you save a specific new value of 80, the next auto-generated value will start at 81.
Open Table in Spreadsheet	To create new records, leave the identity key column value blank. For each row in which the identity column is left blank, a new record is added to the table with an automatically generated ID value.
Imports	To create new records, do either of the following: Leave the identity key column unmapped in the import. For each record in the import temp table, a new record is added to the destination table with an automatically generated ID value. Map the identity key column, but leave the value blank in the import source data. For each record in the import temp table with a blank value, a new record is added to the destination table with an automatically generated ID value. Alternatively, it is also possible to create new records by mapping the identity key column and populating it with specific values that do not already exist in the table. In this case, the identity value for new records is not auto-generated but instead uses the value from the import temp table. If new records are created with specific values, note that the identity "seed" value is reset to the largest inserted value (if that value is greater than the previous seed value). For example, if the last auto-generated value was 20, but you import a specific new value of 80, the next auto-generated value will start at 81. For more information, see Importing data into tables with identity columns.

Data rows with identity columns can be deleted as normal using features such as Open Table in Spreadsheet or Zero/Delete Table Data. However, if the identity column is the key column of a plan code table for an on-demand file group, then you should use the Delete Plan Files command to delete the record instead. This feature deletes the record, the associated plan file, and any associated data in other tables.

NOTE: When using an identity column with an on-demand file group, the Show On List column can be used as an alternative to deleting an ID record. If Show On List is set to False, the associated plan file will no longer display in file group dialogs, but the record still remains in the table.

If an identity record is deleted, the ID number is not reused when numbers are automatically generated. However, if you create a new record with a specific value (using either Save Type 1 or an import), it is possible to create a new record that uses a previously deleted value.