Unique identifier fields in database tables
All tables and feature classes used in ArcGIS must contain a field that uniquely identifies each row or feature.
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 this requirement.
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.
When you drag a database table from the Catalog tree and place it in ArcMap 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 open the query definition and choose a different field or set of fields to use as the unique identifier. See Choosing a unique identifier field for instructions.
Using 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 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 ObjectID 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.
Using 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 sees these records 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 a feature class that uses a composite unique identifier as an editable feature service.