Auto-Incremental Fields (FireDAC)
Go Up to Editing Data (FireDAC)
Contents
Generalities
FireDAC allows the insertion of a new record with an auto-incrementing column and getting back a new value of this column. That works for immediate updates, as well as for cached updates. Depending on the DBMS, the auto-incrementing fields may be implemented either using a special IDENTITY (or similar) column data type, or using a generator (or a sequence) and a table trigger:
| DBMS | Auto-incrementing column implementation | Recognition |
|---|---|---|
| Advantage Database | AUTOINC data type | Automatic as auto-incrementing data type. |
| Firebird |
|
Automatic and manual specifying. See below. |
| InterBase | Generator and BEFORE INSERT trigger | Automatic and manual specifying. See below. |
| IBM DB2 | GENERATED AS IDENTITY data type | Automatic as auto-incrementing data type. |
| Informix | SERIAL data type | Automatic as auto-incrementing data type. |
| MS Access | COUNTER data type | Automatic as auto-incrementing data type. |
| MS SQL Server | IDENTITY data type | Automatic as auto-incrementing data type. |
| MySQL | AUTO_INCREMENT data type | Automatic as auto-incrementing data type. |
| Oracle |
|
|
| PostgreSQL | SERIAL data type | Automatic as auto-incrementing data type. |
| SQL Anywhere | IDENTITY data type | Automatic as auto-incrementing data type. |
| SQLite | INTEGER PRIMARY KEY AUTOINCREMENT data type | Automatic as auto-incrementing data type. |
| Sybase Adaptive Server Enterprise | IDENTITY data type | Automatic as auto-incrementing data type. |
| Teradata Database | GENERATED AS IDENTITY data type | Automatic as auto-incrementing data type when ExtendedMetadata is True.
|
Automatic Recognition
FireDAC automatically recognizes a column of an auto-incrementing data type, and defines it to dtIntXxx, [caAutoInc, caReadOnly, caAllowNull]. This leads to TField setup:
- TField.DataType = ftAutoInc (TFDAutoIncField) when dtInt32 or dtUInt32; otherwise one of the numeric ftXxxx data types;
- TField.Required = False;
- TField.ReadOnly = True;
- TField.ProviderFlags = [pfInWhere], or [pfInWhere, pfInKey] when the column is part of a primary key.
FireDAC automatically recognizes limited sets of the Firebird auto-incrementing columns, and sets them to dtIntXxx, [caAutoInc, caAllowNull] when:
- extended metadata is enabled;
- a table has a BEFORE INSERT trigger;
- the trigger depends on a single column and a single generator. This column is recognized as auto-incrementing.
Manual Specifying
For Oracle, other Firebird / Interbase cases, and other columns, the auto-incrementing mode may be specified by the programmer, using one of the following options:
- Set UpdateOptions.AutoIncFields to the list of the auto-incrementing column names. When a column is of dtInt32 / dtUInt32 data type, then the TFDAutoIncField field will be created.
- Create a TFDAutoIncField field at design or at run time. The column must be of ftAutoInc / ftInteger / ftLongWord field type. For other data types, consider using data type mapping to TargetDataType =
dtInt32. - Set TField.AutoGenerateValue to
arAutoIncfor an auto-incrementing field. This method does not create a TFDAutoIncField, it works with any field type, and may require additional field properties setup, like setting ProviderFlags, Required and ReadOnly.
All these methods finally lead to TField.AutoGenerateValue = arAutoInc.
Client Auto-incrementing
By default, FireDAC uses the client side auto-incrementing for the auto-incrementing columns. After calling the dataset's Insert / Append method, the auto-incrementing column will get the -1 value. With each next method call, the value will be incremented with the -1 (negative) step. The purpose is to distinguish the client assigned values and the DBMS assigned values.
When UpdateOptions.RefreshMode <> rmManual, after posting a new record the auto-incrementing column will get an actual positive value.
The TFDAutoIncField properties allows the adjustment of the client side auto-incrementing:
- ClientAutoIncrement to enable or disable;
- AutoIncrementSeed to specify an initial value;
- AutoIncrementStep to specify a step.
Client Sequence Filling
When a DBMS supports sequences or generators (here the terms are synonyms), then FireDAC may fill an auto-incrementing column from a sequence on a client. This is an alternative to client-side auto-incrementing.
The auto-incrementing column must have pfInUpdate in TField.ProviderFlags and one of the UpdateOptions.FetchGeneratorsPoint values:
gpImmediate--a subsequent value is moved from a generator in the TDataSet.Insert / Append method. And the value is already accessible in the TDataSet.OnNewRecord event handler. When the TDataSet.Cancel method is called, then the value is lost.gpDeferred--a subsequent value is moved in the TDataSet.Post method.
Set UpdateOptions.GeneratorName or TFDAutoIncField.GeneratorName to a generator name, which will be used to get the next sequence value.
Universal Setup
The following auto-incrementing column setup will work with any DBMS, including supporting native auto-incremental columns, and using sequences and triggers:
- When UpdateOptions.FetchGeneratorsPoint =
gpNone, the client sequence filling is disabled. - When pfInUpdate is the value of TField.ProviderFlags, the column is excluded from the INSERT / UPDATE clause.
- When FetchOptions.Items is set to fiMeta, the column is recognized as auto-incremental.
For Oracle, Firebird, and Interbase, it is required to manually specify auto-incremental columns. For databases supporting native auto-incremental data types, no additional setup is required. But specifying it will work in both cases.
Master-Detail Handling
When master and detail datasets are linked by an auto-incrementing master column and cached updates is enabled, then consider using the Centralized Cached Updates mode with propagation of changes from master to detail datasets.
See Also
Samples
- FireDAC Autoinc Fields sample