Auto-Incremental Fields (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Editing Data (FireDAC)

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
  • Generator and BEFORE INSERT trigger
  • Firebird 3.0: GENERATED BY DEFAULT AS IDENTITY
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
  • Sequence and BEFORE INSERT FOR EACH ROW trigger
  • Oracle 12c and higher: GENERATED AS IDENTITY data type
  • Manual specifying. See below.
  • Automatic as auto-incrementing data type.
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:

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 arAutoInc for 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:

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:

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:

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