Choose a unique identifier field

All layers in ArcGIS—including query layers, layers from geodatabases, and layers from databases—require a unique identifier.

If you define a query layer on a geodatabase table or feature class, the table will always have a unique identifier field: the ObjectID field.

In most cases, though, you create query layers to access feature classes and tables in a database or drag a database layer directly into an ArcGIS Desktop client. It is standard practice for database tables to contain a unique identifier field; therefore, your database tables likely already contain a field you can use as a unique identifier.

To be used as a unique identifier in ArcGIS, a field must be not null, contain unique values, and be one of the following data types:

• Integer (positive values only)
• String
• GUID
• Date

Since the value in the unique identifier field uniquely identifies a row or feature object within ArcGIS, values in that field must always be unique and not null. It is your responsibility to guarantee that values in this field meet these requirements.

Note:

You will receive an error if ArcGIS encounters a null value, but ArcGIS does not enforce the uniqueness of values in the unique identifier field used in a query layer or database table. If ArcGIS encounters a nonunique value, no error is returned; however, you may see inconsistent results in selection sets or other queries in ArcGIS.

If ArcGIS cannot detect a qualifying unique identifier, you will be prompted to choose a field or fields to use when you drag a database table into ArcMap or when you validate a new query layer definition. In this example, ArcGIS could not find an integer, not-null field to use for a unique identifier. You must choose a field or combination of fields that you know is not null and unique to be used as a unique identifier for the rows in the table.

If a qualifying field is present, ArcGIS sets the first not-null field it finds as the unique identifier field by default when you drag a database table from the Catalog tree and place it in ArcMap or validate a query layer definition. This is often an adequate value to use as the unique identifier field, but you can choose another qualifying field or fields from the Unique Identifier Field(s) list to be used instead. Follow these steps to change the unique identifier fields ArcGIS uses for your database table:

1. In ArcMap, right-click the layer in the table of contents and click Properties.
2. Click the Source tab and click Change Query.

   The Edit Query Layer dialog box opens.

3. Click Show advanced options and click Next.
4. Choose a different qualifying field or set of fields to be used as the unique identifier and click Finish.
5. Click OK to close the Layer Properties dialog box.