Understanding Primary Keys
Summary
- A Primary Key is required by AdapTable for cell identification purposes
- It is the Id of a Column with guaranteed unique, unchanging values
- If no unique column is available, set
autogeneratePrimaryKey— this is a last resort, not the default
Primary Key Column
The Primary Key is set via the (mandatory) primaryKey property in Adaptable Options.
The value provided should either be:
- an AG Grid Column - either the
colIdorfieldproperty
Caution
The column does not need to be visible but it does need to be specified
- available as a data property in AG Grid's data source
Note
- Without a Primary Key, AdapTable would not be able to uniquely identify cells for Cell Summary or Editing
- Nor could it exactly specify in the Cell Changed or Adaptable State Changed Events which cells have been updated
primaryKey
Default: NONE: IS MANDATORY PROPERTYStringKey Uniqueness
It is important that Primary Key Column is one which guarantees to contain unique values.
Deep Dive
Why do Primary Keys have to be Unique?
Key Immutability
It is also important that values in the Primary Key Column do not change during the lifetime of the Application.
Deep Dive
Why do Primary Keys have to be Immutable?
Displaying Warnings
AdapTable logs an error to the console if the Primary Key is missing from both the AG Grid column definitions and the first row of data.
Note
- A Primary Key that exists on the row data (but is not an AG Grid column) does not trigger this message
- You can suppress this, and other missing-column console messages, by setting
showMissingColumnsWarningto false in Column Options
showMissingColumnsWarning
Default: trueBooleanHint
- For additional security you can also set
showMissingPrimaryKeyAlertto true in Alert Options - This displays an Alert (with a Toast Notification) when no column in the grid matches the Primary Key value
showMissingPrimaryKeyAlert
Default: falseBooleanAuto Generated Key
If it is absolutely not possible to provide a Primary Key column of your own, AdapTable can auto-generate one.
Important
- Only use this option as a last resort if there is no column with unique values, as there are significant limitations
- Auto-generated Keys cannot be used in Modules (e.g. Free Text Columns) which store cell details between sessions
Simply set autogeneratePrimaryKey property to true in Adaptable Options and AdapTable will create - and manage - its own internal Primary Key.
autogeneratePrimaryKey
Default: falseBooleanLimitations
There are a few important limitations when using an auto generated Primary Key:
- Modules which require persistable, consistent Primary Key values to save data are not available, including:
- Live Data updates from Excel back to AdapTable (when using OpenFin) will not work (as Excel needs the column to be in the exported data)
- If using an auto-generated key, the only safe way to load grid data, add Grid Rows or Update Grid Rows is through AdapTable's Grid API; only use the following methods for any data manipulation you require:
loadGridData- for initial (and replacement) data loadaddGridData- to add new rowsupdateGridData- to update rowsdeleteGridData- to remove rows
Note
- This restriction is required because AdapTable needs to intercept all data-related methods
- This will safely ensure that Primary Keys are properly created and then kept correctly updated
Developer Guide
Using an auto generated Primary Key
Using an auto generated Primary Key - if it is absolutely necessary to do so - requires a couple of changes in the AdapTable setup:
const adaptableOptions: AdaptableOptions = {
primaryKey: '', // set empty key
userName: 'Demo User',
adaptableId: 'Basic Setup',
autogeneratePrimaryKey: true, // auto-generate key
initialState: initialState,
};There are 2 main changes that need to be made when setting up Adaptable Options:
- provide no value to the
primaryKeyvalue - set the
autogeneratePrimaryKeyproperty to true
// Update the Grid Data using the Adaptable API
adaptableApi.gridApi.updateGridData([changedRow]);When you are using an auto generated Primary Key you need to provide row data via methods contained in Grid Api
- This Demo uses an Auto Generated Primary Key
- It also has a Custom Button which updates the Price in the First Row - using the
updateGridDatafunction in Grid API
Expand to see how this was created
- Open the Settings Panel and note that the Notes, Comments and FreeText Column Modules are not visible
FAQ
Can we provide a composite Primary Key? At the moment it is not possible to provide a Primary key composing more than one column. However this might be available in future versions of AdapTable.
AdapTable Resources
- See the AdapTable (Vanilla) Integration