Each row in a database table must be uniquely identifiable to be used in Insights. The following logic is used to determine which columns are used 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. It is best practice 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, a warning icon appears when the table is selected to add to a workbook, and a unique identifier must be applied. A unique identifier is a field or a combination of fields that uniquely identifies every row in your data. If a single field is used, the field must contain only unique values and cannot contain null values. If multiple fields are used, the individual fields do not require only unique values, but the combination of the rows must always be unique.
A unique identifier can be created using string or integer columns. Date, float, and double columns cannot be chosen as the unique identifier.
Examples
The following table contains sales data for a retail chain. The table includes a store number, the state where the store is located, and the total amount in sales. Two of the fields, Store number and Total sales, contain all unique vales and no nulls. The other field, State, does not contain unique values. In this case, the Store number field should be used as the unique identifier, since each store is only listed once and you know the store number will always be unique for each store.
| Store number | State | Total sales |
|---|---|---|
1001 | Colorado | 1,583,296.29 |
1002 | Colorado | 1,938,495.84 |
1003 | Wyoming | 1,439,752.57 |
1004 | Montana | 2,583,485.83 |
1005 | Montana | 928,573.67 |
If a single field does not fit the requirements for a unique identifier, or if you are not confident that the values in the field will always remain unique, you can combine multiple fields to create unique entries. The following table includes the number of items in stock at each store. Unlike in the previous example, the Store number field is not unique, because there are multiple items in stock per store. The Item ID field is also not unique. The Quantity field is currently unique, but the number of items in stock at a store is likely to change and will not always be unique, so that field should not be used as an identifier. A better identifier would be a combination of the Store number and Item ID fields, since each item is only listed once per store.
| Store number | Item ID | Quantity |
|---|---|---|
1001 | 123456 | 20 |
1001 | 234567 | 5 |
1001 | 345678 | 16 |
1002 | 123456 | 11 |
1002 | 234567 | 0 |
1002 | 345678 | 21 |
Choose a unique identifier
Complete the following steps to apply a unique identifier from the database table:
- Click the warning icon
.The Choose a unique identifier window appears. The window displays the nonspatial fields in the database table that do not contain null values, as well as the field type.
- Do one of the following:
- If you know which column or combination of columns is best to create a unique identifier, select the fields.
- If you do not know which column or combination of columns is best to create a unique identifier, click the Analyze button to scan the table for columns that contain all unique values. Columns containing unique values are indicated using the following icon:
- If the table does not have columns that can be used as a unique identifier, or if you do not want to create a unique identifier from the existing fields, you can choose Copy the database table and create a primary key.
- Click Apply.
Create a primary key
Some database tables do not include columns that can be used as a unique identifier. Therefore, a primary key must be created before the table can be used in Insights. For those tables, you must select Copy the database table and create a primary key.
Insights does not edit the data in your database tables, including adding a primary key column. A copy of the database table is created before the primary key is applied. The location of the copied database table depends on the permissions for the database connection. For read-write connections, the copied table is stored in the database. For read-only connections, the table is copied and stored in the Insights workbook. Datasets copied to a workbook will have the same functionality as hosted feature layers or uploaded files.