Home >

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.

Editing identity columns

Identity columns are read-only, except when performing processes that cause an ID to be automatically generated. This means that new rows of data can be saved to the table, but you cannot manually specify an ID number or edit existing values in the identity column.

In the primary use case, values are added to the identity key column by use of the "add file" feature for on-demand file groups. However, you can also create new identity records by using Save Type 1 or Open Table in Spreadsheet. The behavior is different for the different save types:

• When using Save Type 1, you must 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 will be added to the table, and the identity value will be automatically generated as normal.

• When using Open Table in Spreadsheet, you must enter a valid integer value into the identity key column, and that value must not match any existing values in the column. When the table is saved, the entered value is ignored and instead a new identity value will be automatically generated for the new record. You cannot leave the identity column blank in this environment, as an error will result.

Data rows with identity columns can be deleted as normal using features such as Open Table in Spreadsheet or Zero/Delete Table Data. If an ID number is deleted from a table, it is not reused. 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.

Because the identity column is treated as read-only, it is not possible to import data into identity columns.