The Schema Tool allows you to designate the primary key column for your relational table view. By default, the primary key is set to the _ID column, which contains identifiers generated by your database that are used to define rows. Although the _ID column typically provides a set of unique row identifiers that work seamlessly with your application, you may want to designate a different primary key depending on your server configuration or the conceptual design of your data.

If you connect to a shard server that does not use the _ID field as the shard key, you must designate a new primary key to ensure accurate joins on reads and avoid damaging data on writes. See your database documentation for details on shard keys.

Based on the conceptual design of your data, you may prefer to designate a user-generated column as your primary key. User generated columns consist of data provided by users that may have significance external to the database, such as order numbers or employee identification numbers. When designated as the primary key, these columns can provide a real world association between your data and your identifiers, which may better complement the design of your data and your operational needs. Designating a new primary key allows you to further tailor the relational view to match your data concepts by permitting you to hide the _ID column from relational tables. In the default settings, the Schema Tool prohibits hiding the _ID column because it is designated as the primary key. See Removing Columns for additional information.

CAUTION: If you designate a primary key column other than _id in the Schema Tool, verify that all values in the column are unique when fetched as the mapped SQL type of the column before disabling the Read Only connection option. Executing write operations against data with duplicate primary keys can produce undesired results. It is also critical that all values of the primary key column are able to be represented as the SQL type to which the column is mapped without any conversion errors or loss of precision. When conversion errors occur, the driver converts incompatible values to null, creating the potential for duplicate identifiers of null.

To designate a primary key:

  1. Navigate to the main display.

    schema tool mark as primary key

  2. In the Available Schemas pane, expand the tree to display your parent table's columns.
  3. Right click on the column that you want to designate as your new primary key; then, select Mark As Primary Key.
    Note: Columns mapped from complex data types, such as arrays and objects, cannot be designated as the primary key.
    The icon for the column you selected will change to the ID icon , indicating that it is now the primary key. In a normalized view, foreign key columns will automatically be updated and renamed to reflect the new primary key.
  4. Save your changes from the main display. Click the Save button or select File>Save to save the modified schema.