The primary key for parent tables are determined heuristically from the top-level fields in the document. When sampling, the driver attempts to find the first outermost simple column to designate as the primary key. Columns are then evaluated using the following rules to determine the most viable candidate:

  • If sampling reveals a duplicate value, the column is not considered a good candidate
  • If sampling reveals a null or empty value, the column is not considered a good candidate
  • If sampling reveals certain statistical patterns in the content of the data, the column may be discarded as a candidate
  • If sampling reveals a value of one of the following data types, the column is not considered a good candidate:
    • Array
    • Binary
    • Boolean
    • Date
    • Decimal
    • JSON
    • Time
    • Timestamp
    • String types greater than 32 characters (URLs greater than 128 characters)
  • If sampling reveals a column name contains a common paging parameter, the column is not considered a good candidate
  • If no top-level column is available, nested columns inside objects may be considered
  • If the search fails to discover a viable candidate, the driver generates a primary key column named ROWID. When the driver generates a primary key column:
    • The driver flattens and maps the objects from the document into a single table. No child tables will be mapped from the document.
    • The primary key values are based on a composite of values in the row to create a unique value. Note that because the primary key is based on data, the primary key value might change between sessions if the data is modified.
    • The values of the ROWID column are only stored locally and persist for only the life of the session.

Note that this is just an overview of the rules employed by the driver. Additional and more subtle interactions occur when the driver encounters complex types or unusual data structures or values.

Note: You can also designate a primary key other than the default using the Model file or the Autonomous REST Composer. For more information, see "Primary key" to directly edit the Model file and "Customizing your schema" to use the Autonomous REST Composer.

Designating a primary key

You can also designate a primary key other than the default using the Model file or the Autonomous REST Composer. For more information, see:
  • "Primary key" to directly edit the Model file;
  • "Customizing your schema" to use the Autonomous REST Composer.

In addition, when selecting your new primary key, you can review a list of potential primary key candidates by querying the INFORMATION_SCHEMA.SYSTEM_SAMPLING_STATUS system table. For example: 

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_SAMPLING_STATUS

The table contains a PRIMARY_KEY_CANDIDATES column, which includes a list of potential primary key candidates. The candidates are ranked in order by the driver starting with the best candidate. For more information on the INFORMATION_SCHEMA.SYSTEM_SAMPLING_STATUS system table, see "Reviewing the status of your endpoints."