Creating Identifier Columns
Some databases provide a special column kind which is known as "identifier", or "auto-increment" column. When a column is defined as an identity column, the database will automatically generate a unique, incremental value for this column whenever a new row is inserted into the table. Support for identifier columns varies by database.
| Note: | The Design Editor does not support defining identifier columns for the following database types: Firebird, Informix, Oracle, PostgreSQL, Progress OpenEdge, Teradata, and SQLite. |
When supported by the database, a property called Auto Increment (or Identity, in some databases) becomes available in the Properties window when you click the column in the Design Editor (provided that the column is of numeric type, see "Prerequisites" below). Databases such as IBM DB2 and SQL Server also let you specify the Seed and Increment properties, in addition to the Identity property.
Properties window (SQL Server database)
Prerequisites:
•Typically, you can create identity columns at the table creation time, not later. Some databases (Microsoft Access, Microsoft SQL Server) let you specify a column as identity when you add a new column to an existing table.
•The column must not be nullable. In other words, the Nullable check box must not be selected in the Properties window (or in the table design).
•The column must have a numeric type (such as INT, INTEGER, SMALLINT, BIGINT, or even DECIMAL with a scale of 0). Other numeric types may qualify, depending on the database.
To create an identity column:
1.In the Design Editor or in the Online Browser, select the column you want to use an as identity column.
2.In the Properties Window, select the Identity check box (or the Auto Increment check box, depending on the database).
| Note: | In Microsoft Access, the column data type will be automatically converted to COUNTER when you select the Auto Increment check box. This data type is specific to Microsoft Access, it ensures that the value will be auto-incremented. |
3.If applicable, change the default values in the Seed and Increment fields. Seed specifies the initial value that will be generated by the database while Increment specifies by how much the initial value should be incremented on each insert. Normally, these values are 1 and 1, respectively.
As always when making changes to the database structure, a change script is generated which must be executed in order to actually create the identifier column in the database. DatabaseSpy performs a validation of the column definition that will be used for the identifier column. The image below illustrates a script where validation has failed because the column is nullable and not of numeric type:
A valid script, on the other hand, could look like:
If the script is valid and you wish to commit the changes to the database, click Execute 
in the Database Structure Change Script window.