# Understanding Primary Keys

Canonical page: https://www.adaptabletools.com/docs/getting-started-primary-key

- 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](https://www.adaptabletools.com/docs/technical-reference-adaptable-options/index.md).

The value provided should either be:

- an AG Grid Column - either the `colId` or `field` property

The column does not need to be visible but it <b>does</b> need to be specified

- available as a data property in AG Grid's data source

- Without a Primary Key, AdapTable would not be able to uniquely identify cells for [Cell Summary](https://www.adaptabletools.com/docs/handbook-summarising/index.md) or [Editing](https://www.adaptabletools.com/docs/handbook-editing/index.md)
- Nor could it exactly specify in the [Cell Changed](https://www.adaptabletools.com/docs/technical-reference-editing/index.md) or [Adaptable State Changed](https://www.adaptabletools.com/docs/dev-guide-adaptable-state-events/index.md) Events which cells have been updated

### `primaryKey`

Name of a Column in the Data Source guaranteed to contain unique values

It is highly recommended that this is provided.

The value provided does not need to be a column in the grid but must be in the data source.

If you absolutely cannot provide one, then leave blank and set `autogeneratePrimaryKey` to true.

```ts {2}
const adaptableOptions: AdaptableOptions = {
  primaryKey: 'tradeId',
};
```

### Key Uniqueness

It is important that Primary Key Column is one which **guarantees to contain unique values**.

### Why do Primary Keys have to be Unique?

There are a few reasons why Primary Keys must be unique.

Perhaps the most important consideration is that if the `getRowId` property hasn't been provided in AG Grid's `GridOptions`, AdapTable will supply one using the Primary Key value.

Having a Primary Key column with duplicate values will result in duplicate or inconsistent indexing.

Additionally row selection in both [AG Grid](https://ag-grid.com/javascript-data-grid/viewport/#selection) and [AdapTable](https://www.adaptabletools.com/docs/handbook-selecting/index.md) assumes unique row keys.

### Key Immutability

It is also important that values in the Primary Key Column do not change during the lifetime of the Application.

### Why do Primary Keys have to be Immutable?

Many things in AdapTable are stored using the Primary Key.

This includes objects, like Notes, which are stored between Sessions, and objects which are stored during a Session (e.g. which cells are flashing).

If a Primary Key changes once its been created, it will not be possible to identify these objects any longer.

### 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.

- 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 `showMissingColumnsWarning` to _false_ in [Column Options](https://www.adaptabletools.com/docs/technical-reference-column/index.md)

### `showMissingColumnsWarning`

Log warning to console if AdapTable cannot find a column

AdapTable, by default, logs a console message whenever a column referenced in [Initial Adaptable State](https://www.adaptabletools.com/docs/technical-reference-initial-state/index.md) or [Adaptable Options](https://www.adaptabletools.com/docs/technical-reference-adaptable-options/index.md) does not exist.

Set this property to _false_ to suppress that message.

```ts {4}
// Don't display console messages if a referenced Column doesn't exist
const adaptableOptions: AdaptableOptions = {
  columnOptions: {
    showMissingColumnsWarning: false,
  },
};
```

- For additional security you can also set `showMissingPrimaryKeyAlert` to _true_ in [Alert Options](https://www.adaptabletools.com/docs/getting-started-primary-key/index.md#displaying-warnings)
- This displays an [Alert](https://www.adaptabletools.com/docs/handbook-alerting/index.md) (with a [Toast Notification](https://www.adaptabletools.com/docs/ui-tutorial-using-toast-notifications/index.md)) when no column in the grid matches the Primary Key value

### `showMissingPrimaryKeyAlert`

Shows Alert if Primary Key column is not present or incorrect

This property will cause AdapTable to display an Alert if the Primary Key column in Adaptable Options is missing or wrongly applied.

```ts {4}
// Shows Alert if Primary Key column in Adaptable Options is not present or incorrect
const adaptableOptions: AdaptableOptions = {
  alertOptions: {
    showMissingPrimaryKeyAlert: true,
  },
};
```

## Auto Generated Key

If it is absolutely not possible to provide a Primary Key column of your own, AdapTable can auto-generate one.

- Only use this option as a **last resort** if there is no column with unique values, as there are [significant limitations](#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](https://www.adaptabletools.com/docs/technical-reference-adaptable-options/index.md) and AdapTable will create - and manage - its own internal Primary Key.

### `autogeneratePrimaryKey`

Request AdapTable to create a generated Primary Key

Sets whether Adaptable should dynamically generate the Primary Key instead of using the `primaryKey` property.

If setting this property to _true_, ensure also to set `primaryKey` to an empty string

```ts {2,3}
const adaptableOptions: AdaptableOptions = {
  autogeneratePrimaryKey: true,
  primaryKey: '',
};
```

### Limitations

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:
  - [Free Text Columns](https://www.adaptabletools.com/docs/handbook-freetext-column/index.md)
  - [Notes](https://www.adaptabletools.com/docs/handbook-notes/index.md)
  - [Comments](https://www.adaptabletools.com/docs/handbook-comments/index.md)
- Live Data updates **from** Excel back to AdapTable (when using [OpenFin](https://www.adaptabletools.com/docs/integrations-openfin/index.md)) 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](https://www.adaptabletools.com/docs/handbook-managing-grid-data-loading/index.md), [add Grid Rows](https://www.adaptabletools.com/docs/handbook-managing-grid-data-rows-add/index.md) or [Update Grid Rows](https://www.adaptabletools.com/docs/handbook-managing-grid-data-rows-update/index.md) is through AdapTable's [Grid API](https://www.adaptabletools.com/docs/handbook-managing-grid-data/index.md); only use the following methods for any data manipulation you require:
  - `loadGridData` - for initial (and replacement) data load
  - `addGridData` - to add new rows
  - `updateGridData` - to update rows
  - `deleteGridData` - to remove rows

- 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

### 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:

There are 2 main changes that need to be made when setting up Adaptable Options:

- provide no value to the `primaryKey` value
- set the `autogeneratePrimaryKey` property to **true**

```ts {2,5}
const adaptableOptions: AdaptableOptions = {
  primaryKey: '', // set empty key
  userName: 'Demo User',
  adaptableId: 'Basic Setup',
  autogeneratePrimaryKey: true, // auto-generate key
  initialState: initialState,
};
```

When you are using an auto generated Primary Key you need to provide row data via methods contained in [Grid Api](https://www.adaptabletools.com/docs/handbook-managing-grid-data/index.md)

```ts {2}
// Update the Grid Data using the Adaptable API
adaptableApi.gridApi.updateGridData([changedRow]);
```

**Example: Auto-Generated Primary Keys**

Using Primary Keys dynamically generated by AdapTable

- This Demo uses an Auto Generated Primary Key
- It also has a Custom Button which updates the Price in the First Row - using the `updateGridData` function in [Grid API](https://www.adaptabletools.com/docs/handbook-managing-grid-data/index.md)
### Expand to see how this was created

```ts
export const adaptableOptions: AdaptableOptions = {
  primaryKey: '',
  adaptableId: 'Auto Generated Primary Key',
  autogeneratePrimaryKey: true,
};
```

- Open the Settings Panel and note that the Notes, Comments and FreeText Column Modules are not visible

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {
  AdaptableButton,
  AdaptableOptions,
  CustomToolbarButtonContext,
} from '@adaptabletools/adaptable';
import {IRowNode} from 'ag-grid-enterprise';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: '',
  adaptableId: 'Auto Generated Primary Key',
  autogeneratePrimaryKey: true,
  dashboardOptions: {
    customToolbars: [
      {
        name: 'Custom',
        title: 'Custom',
        toolbarButtons: [
          {
            label: 'Increase First Row "Rating"',
            onClick: (
              button: AdaptableButton<CustomToolbarButtonContext>,
              context: CustomToolbarButtonContext
            ) => {
              const firstNode: IRowNode | undefined =
                context.adaptableApi.gridApi.getFirstRowNode();
              let firstRow = firstNode?.data;
              let firstRowRating = firstRow['Rating'];
              const newRating =
                firstRowRating + Math.floor(Math.random() * 10) + 1;
              firstRow['Rating'] = newRating;
              // update Adaptable using Grid Api
              context.adaptableApi.gridApi.updateGridData([firstRow]);
            },
          },
        ],
      },
    ],
  },
  initialState: {
    Dashboard: {
      Tabs: [
        {
          Name: 'Demo',
          Toolbars: ['Custom'],
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'license',
            'has_projects',
            'has_pages',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'github_watchers',
            'week_issue_change',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

**`src/columnDefs.ts`**

```ts
import {ColDef} from 'ag-grid-enterprise';

export const columnDefs: ColDef[] = [
  {
    headerName: 'Name',
    field: 'Name',
    filter: true,
    editable: false,
    sortable: true,
    cellDataType: 'text',
  },
  {
    headerName: 'Rating',
    field: 'Rating',
    filter: true,
    editable: false,
    sortable: true,
    cellDataType: 'number',
  },
  {
    headerName: 'Year',
    field: 'Year',
    filter: true,
    editable: false,
    sortable: true,
    cellDataType: 'number',
  },
  {
    headerName: 'Institutions',
    field: 'Institutions',
    filter: true,
    editable: false,
    sortable: true,
    cellDataType: 'textArray',
  },
  {
    headerName: 'Country',
    field: 'Country',
    filter: true,
    editable: false,
    sortable: true,
    cellDataType: 'text',
  },
  {
    headerName: 'CountryCode',
    field: 'CountryCode',
    filter: true,
    editable: false,
    sortable: true,
    cellDataType: 'text',
  },

  {
    headerName: 'Email',
    field: 'Email',
    filter: true,
    editable: false,
    sortable: true,
    cellDataType: 'text',
  },
];
```

**`src/rowData.ts`**

```ts
export const rowData = [
  {
    Name: 'Giorgio Parisi',
    Year: 2021,
    Institutions: ['Sapienza', ' Columbia'],
    Country: 'Italy',
    CountryCode: 'ITA',
    Email: 'giorgio.parisi@mail.com',
    Rating: 89,
  },
  {
    Name: 'Klaus Hasselman',
    Year: 2021,
    Institutions: ['Hamburg', 'Max Planxk', 'Uni of California'],
    Country: 'Germany',
    CountryCode: 'DEU',
    Email: 'klaus.hasselman@yahoo.com',
    Rating: 74,
  },
  {
    Name: 'Syukor Manabe',
    Year: 2021,
    Institutions: ['Princeton', 'Nagoya'],
    Country: 'Japan',
    CountryCode: 'JPN',
    Email: 'syukor.manabe@outlook.com',
    Rating: 92,
  },
  {
    Name: 'Andrea Ghez',
    Year: 2020,
    Institutions: ['Uni of California'],
    Country: 'United States',
    CountryCode: 'USA',
    Email: 'andrea.ghez@mail.com',
    Rating: 85,
  },
  {
    Name: 'Reinhard Genzel',
    Year: 2020,
    Institutions: ['Max Planck'],
    Country: 'Germany',
    CountryCode: 'DEU',
    Email: 'reinhard.genzel@mail.com',
    Rating: 68,
  },
  {
    Name: 'Roger Penrose',
    Year: 2020,
    Institutions: ['Columbia', 'Princeton', 'Syracuse'],
    Country: 'United Kingdom',
    CountryCode: 'GBR',
    Email: 'roger.penrose@yahoo.com',
    Rating: 71,
  },
  {
    Name: 'Didier Queloz',
    Year: 2019,
    Institutions: ['University of Cambridge', 'Geneva'],
    Country: 'Switzerland',
    CountryCode: 'CHE',
    Email: 'didier.queloz@mail.com',
    Rating: 87,
  },
  {
    Name: 'Michel Mayor',
    Year: 2019,
    Institutions: ['Geneva', 'Columbia'],
    Country: 'Switzerland',
    CountryCode: 'CHE',
    Email: 'michel.mayor@yahoo.com',
    Rating: 94,
  },
  {
    Name: 'Jim Peebles',
    Year: 2019,
    Institutions: ['Princeton'],
    Country: 'United States',
    CountryCode: 'USA',
    Email: 'jim.peebles@outlook.com',
    Rating: 66,
  },
];
```

### 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.
