All tables and feature classes used in ArcGIS must contain a field that uniquely identifies each row or feature.
To use a field as a unique identifier in ArcGIS desktop apps, the field must be not null, must contain unique values, and be one of the following data types:
- Integer (32- and 64-bit positive values only)
- String
- GUID
- Date
Values in the 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.
To publish the query layer in a feature layer (a feature service) that references the registered data store, the ID must be a single, not null, unique, autoincrementing, 32-bit, integer field.
When you drag a database table onto a map or validate a query layer definition, ArcGIS sets the first not-null field it finds as the unique identifier field by default. You can use this field, or modify the query definition and choose a different field or set of fields to use as the unique identifier.
Use a single field as a unique identifier
If a single integer field is specified as the unique identifier, ArcGIS uses the values in that field directly to uniquely identify all features and rows in the database table.
If your database table does not have a field that can be used as a unique identifier, and you are using a Dameng, IBM Db2, Microsoft SQL Server, Oracle, or PostgreSQL database, you can run the Add Incrementing ID Field geoprocessing tool to add a unique identifier integer field to the table.
If a single string field is used as the unique identifier, ArcGIS must map those unique values to an integer. This is done in ArcGIS anytime the system needs an Object ID attribute, such as when creating a map selection or opening the attribute table. ArcGIS adds an attribute called ESRI_OID and stores a unique integer value in it. This attribute is only part of the layer definition; the underlying database table is not altered.
Use a composite unique identifier
You can choose a single field or multiple fields to define a unique identifier. If you choose to use multiple fields, the combined values in these fields must be unique. The following example shows two text fields, member_surname and signup_date. Individually, the values in these fields might not be unique. But when used in combination, the values are unique, as shown here:
| member_surname | signup_date |
|---|---|
alfred | 2006-09-28 10:15:41 |
dewey | 2006-09-28 10:15:56 |
johnson | 2000-02-19 09:14:50 |
johnson | 2004-12-08 11:02:32 |
mujan | 2011-07-07 12:44:21 |
The combination of these values will be used as a key to generate a unique integer value, which will be stored in an attribute called ESRI_OID. This attribute is only part of the layer definition; the underlying database table is not altered.
The layer definition for the previous example would include an ESRI_OID column, as shown here:
| member_surname | signup_date | ESRI_OID |
|---|---|---|
alfred | 2006-09-28 10:15:41 | 1 |
dewey | 2006-09-28 10:15:56 | 2 |
johnson | 2000-02-19 09:14:50 | 3 |
johnson | 2004-12-08 11:02:32 | 4 |
mujan | 2011-07-07 12:44:21 | 5 |
Be aware that if the combined values of the fields or individual text field you specify are not unique, ArcGIS interprets these rows as the same and will map them to the same ESRI_OID value.
If your table already contains a field named ESRI_OID, an attribute named ESRI_OID_1 is added to the layer definition.
Note:
You cannot publish an editable feature service (web feature layer) that contains a feature class with a composite unique identifier.