Unique identifiers

Each row in a database table must be uniquely identifiable to be used in Insights. The following logic is used in determining the columns to use to identify uniqueness:

  • Search for a primary key. If one is found, use the columns that define the primary key.
  • Search for an index that is flagged as unique. If one is found, use the columns that define the index.
Tip:

Unexpected results may occur if a unique index is used and null values are included in the selected columns. A best practice is to define a primary key for all tables. If it is not possible to define a primary key, the columns that participate in a unique index should be flagged in the database as not null.

If the above criteria are not met, Insights uses all nonspatial columns in the table to determine the uniqueness of rows. Spatially enabled tables are treated as nonspatial datasets under these conditions.

Each database has a limit on the number of columns that can be used in a primary key. The primary key operation fails if there are duplicate records after all the columns in the table are used or the column limit is reached for the database. The following table summarizes the limits on the number of columns that can be used to create a primary key:

DatabasePrimary key column limit
PostgreSQL

32

Microsoft SQL Server

16

SAP HANA

No limit

Oracle

32