Unique Identifying Fields (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Editing Data (FireDAC)

Generalities

The set of columns uniquely identifying each dataset record is the unique identifying columns. When a dataset is a single table SELECT, then the unique identifying columns are a FROM table primary key. Often an auto-incrementing field is a table primary key.

Usage

Unique identifying fields are used to build WHERE clauses for:

Provision

TFDQuery, TFDTable, TFDMemTable, and TFDCommand automatically retrieve the unique identifying columns (mkPrimaryKeyFields) for the main (first) table in the SELECT ... FROM ... statements, when fiMeta is included in FetchOptions.Items. Note:

  • mkPrimaryKeyFields querying may be time consuming;
  • the application may need to explicitly specify unique identifying columns, when FireDAC fails to determine them correctly.

To explicitly specify columns, exclude fiMeta from FetchOptions.Items, and use one of the following options:

When the application creates persistent fields, then initially TField.ProviderFlags will be set correctly. After that, automatic field setup will not happen, when the DB structure or query is changed. You should manually update ProviderFlags to adjust the column list. Also, if the primary key consists of several fields, then all of them must be included into persistent fields.

Row Identifying Columns

Alternatively, a row identifying column may be included into the SELECT list. When FireDAC founds such columns, it will not retrieve mkPrimaryKeyFields metadata and it will use this column. The supported DBMSs are the following:

DBMS Row identifying column
Firebird DB_KEY
Informix ROWID
Interbase DB_KEY / RDB$DB_KEY
Oracle ROWID
PostgreSQL OID. The table must be created with OIDs.
SQLite ROWID

For example, on Oracle:

SELECT T.*, ROWID FROM Orders T

See Also