Reset Table Auto Increment or Identity Columns

This topic explains possible impacts of XML Export/Import on table auto increment columns, and how to re-sequence the columns whose value approaches the maximum datatype value.

Topics

Topic	Detail	See also
XML Export/Import	XML Export/Import can cause gaps in the numbering sequence of auto increment columns. Each XML Import deletes rows from several tables; the import then adds rows starting from the maximum value of the auto increment column. Repeated XML imports can result in the value of the auto increment approaching the maximum value of the database datatype; for example, SQL Server's int datatype has a maximum value of 2,147,483,647.
Replication	Large auto increment values can also arise where the project originated as an EAP replica or design master. The Jet engine assigns random values for auto increment columns with each XML Import into the project. These random values can approach the maximum range of the repository data type, which could present a problem when the EAP project is transferred to a repository.	Replication

Topic

Detail

See also

XML Export/Import

XML Export/Import can cause gaps in the numbering sequence of auto increment columns.

Each XML Import deletes rows from several tables; the import then adds rows starting from the maximum value of the auto increment column.

Repeated XML imports can result in the value of the auto increment approaching the maximum value of the database datatype; for example, SQL Server's int datatype has a maximum value of 2,147,483,647.

Replication

Large auto increment values can also arise where the project originated as an EAP replica or design master.

The Jet engine assigns random values for auto increment columns with each XML Import into the project.

These random values can approach the maximum range of the repository data type, which could present a problem when the EAP project is transferred to a repository.

Replication

Access

Ribbon

Configure > Model > Check Integrity > Reset IDs

Re-sequence auto increment columns

Step	Action
1	Open the project.
2	Select the 'Reset IDs' menu option. A dialog displays listing all non-empty tables that contain an auto increment or identity column. The 'Rows' column shows the number of rows in the table The 'Maximum ID' column shows the current maximum value of the auto increment column The 'Action' column shows either 'No Action' or 'Reset', depending on how close the column value is to the datatype's maximum Tables requiring a reset are automatically selected in the list.
3	Click on the Go button to reset the auto increment column values.

Step

Action

Open the project.

Select the 'Reset IDs' menu option.

A dialog displays listing all non-empty tables that contain an auto increment or identity column.

The 'Rows' column shows the number of rows in the table
The 'Maximum ID' column shows the current maximum value of the auto increment column
The 'Action' column shows either 'No Action' or 'Reset', depending on how close the column value is to the datatype's maximum

Tables requiring a reset are automatically selected in the list.

Click on the Go button to reset the auto increment column values.

Notes

Connection to the project must be via a direct connection; IDs cannot be reset while the connection to the repository is via Cloud Services
Project Auditing must be disabled before IDs can be reset
MySQL repositories will require re-starting of the MySQL server so that the reset auto increment columns start from the reset value
In the Corporate, Business and Software Engineering, System Engineering and Ultimate editions of Enterprise Architect, if security is enabled you must have Check Data Integrity permission to reset IDs

Learn more

Auditing