Unique Identifying Fields (FireDAC)
Go Up to Editing Data (FireDAC)
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.
Unique identifying fields are used to build WHERE clauses for:
- posting updates (Edit/Post) and deleting (Delete) from a dataset, when UpdateOptions.UpdateMode is
- refreshing the current dataset record (RefreshRecord);
- selecting deferred BLOB (FetchBlobs) values and nested datasets (FetchDetails).
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:
- set UpdateOptions.KeyFields to a ';' separated list of column names;
- include pfInKey into the corresponding TField.ProviderFlags property.
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|
|Interbase||DB_KEY / RDB$DB_KEY|
|PostgreSQL||OID. The table must be created with OIDs.|
For example, on Oracle:
SELECT T.*, ROWID FROM Orders T