Auto-increment fields/columns are widely used in databases for decades. They allow to automatically generate a unique number when a new record is inserted to a table.
In Exportizer, when exporting data to SQL Script and Database export formats, it is possible to define auto-increment fields for the target part of the field mappings. Usually, you do this when the target table does not exist yet or when it must be recreated during the export operation (that is set by the export mode). Then, when (re)creating the target table, Exportizer uses your field mappings definition to construct and execute the CREATE TABLE SQL command in the target database prior to exporting the data to it.
You can use target auto-increment fields even for updating data in the target table, but you should be very careful doing so. If you need to update data in the target table which has the auto-increment field, and you don't want to update data in it, just exclude the field from the field mappings.
When the target table with auto-increment field must be (re)created during the exporting process, you can either fill this field with incoming source data, or leave it untouched to let the target database fill it with unique values. In the latter case, leave the source part of the corresponding field mapping empty.
To specify the target auto-increment field, you either choose the corresponding type for the field or specify identity attributes depending on what your target database supports. It can be done either in the field mappings grid
or you can double-click any target attribute in the grid and use a special interface for that:
Notes for Different Database Types
Every RDBMS defines their own way to generate unique numbers. It can be special data types or separate sequence based mechanisms.
SQL:2003 standard specifies a unified way to do this: using GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY columns, and since then, some RDBMSs implemented it. ALWAYS identity type is more strict and does not allow user-defined values by default; consider this when exporting data to such columns.
In Exportizer, it is possible to use either classic auto-increment columns or modern identity columns (if supported by target database) in most of cases. Below, there are some notes about using auto-increment and identity columns in target databases of different types.
Please note that many RDBMSs does not allow more than one auto-increment/identity column per table.
SQL Server
In SQL Server, you have an IDENTITY option with two parameters: seed (starting value) and increment, for example:
CREATE TABLE dbo.customer (
id int identity(1000, 1) not null,
name varchar(60) not null
);Here, id column is defined as identity with starting value of 1000 and increment of 1.
When you need to insert user-defined value(s) in such column, you need to execute SET IDENTITY_INSERT <table_name> ON command before inserting. After inserting, SET IDENTITY_INSERT <table_name> OFF command should be executed. Exportizer does that automatically.
MySQL
In MySQL, there is an auto_increment option. By default, the starting value for auto_increment columns is 1, and it will increment by 1 for each new record. You can set the starting value on the table (not column) level, for example:
CREATE TABLE customer (
id integer not null auto_increment,
name varchar(60) not null
) AUTO_INCREMENT=1000;Here, AUTO_INCREMENT=1000 sets the starting value of 1000 for the column id. That can be done by Exportizer if you properly configure the field mappings.
Microsoft Access
In Microsoft Access, you just use a COUNTER or AUTOINCREMENT column type. The starting value is 1, and it will increment by 1 for each new record.
Paradox
For Paradox table, you use a AUTOINC field type. The starting value is 1, and it will increment by 1 for each new record.
PostgreSQL
In PostgreSQL, the classic way to define the auto-increment column is to use serial, smallserial, or bigserial column types. The starting value is 1, and it will increment by 1 for each new record. These default settings can be overridden when altering the corresponding PostgreSQL sequences. While serial-like columns are supported in Exportizer field mappings, there is no way to change the default starting value and increment parameters.
Starting from version 10, PostgreSQL supports SQL:2003 compliant IDENTITY columns, which are more flexible to use. In such a column definition, there are optional parameters which allow you to override the default starting value, increment, minimum value, maximum value and some others, for example:
CREATE TABLE customer (
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT 1 MAXVALUE 1000000 NO CYCLE),
name VARCHAR(60) NOT NULL
);Here, the starting value for id column is defined as 100, maximum value as 1000000 and so on. If you omit the optional part inside the braces and the braces itself, the default auto-increment settings will applied: 1 for starting value, 1 for increment, 1 for minimum value, and the maximum value will be the maximum value specified for the column type.
Exportizer supports defining IDENTITY columns for target PostgreSQL databases along with their sequence settings.
Oracle
In Oracle, the classic way to define the auto-increment column is to use a separate SEQUENCE object. You either explicitly query the sequence next value in INSERT or MERGE commands or do this in a trigger. Working with sequences is not supported by Exportizer.
Starting from version 12, Oracle supports SQL:2003 compliant IDENTITY columns. In addition to standard ALWAYS and BY DEFAULT identity types, Oracle supports BY DEFAULT ON NULL type. Read Oracle documentation to learn the differences. Example:
CREATE TABLE CUSTOMER (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT 1 MAXVALUE 1000000 NOCYCLE),
CUSTOMER_NAME VARCHAR2(60) NOT NULL
);Exportizer supports defining IDENTITY columns for target Oracle databases along with their sequence settings.
Firebird
In Firebird, the classic way to define the auto-increment column is to use a separate GENERATOR object and define a table trigger to give the auto-incremented value for the table. Working with generators is not supported by Exportizer.
Starting from version 3, Firebird supports SQL:2003 compliant IDENTITY columns, but only BY DEFAULT identity type was initially supported. In version 4, ALWAYS type added. In the column definition, you can optionally specify the starting value for the column, for example:
CREATE TABLE CUSTOMER (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2125),
CUSTOMER_NAME VARCHAR(60) NOT NULL
);Exportizer supports defining IDENTITY columns for target Firebird databases along with their starting value.
DB2
In DB2, you can use either sequences or IDENTITY columns. While working with sequences is not supported by Exportizer, it is possible to define IDENTITY columns for target DB2 databases along with their sequence settings.
See also