Today’s Tip

Ensure that you have a unique identifier attribute for each table in your database

Discussion

This is another tip that arises from me doing a task, and realizing I forgot something critical. In this case, the task involved creating a table, trying to perform an operation on said table, and encountering an error or limitation due to a lack of unique identifier attribute.
Refresher time: A Unique Identifier (UniqueID) is an attribute that is guaranteed to be unique for each feature or row in a spatial layer or table. A Globally Unique Identifier (GUID) is one that is unique not just within a single data layer, but within every single layer or table in a database.
The need for such at a basic level is that you need to have a way to identify any particular feature or row, without needing to rely on any combination of attributes. The only caveat to this is when your unique ID is actually a combination of two different attributes or characteristics. This case is not really ideal though, and should be avoided.
In the context of GIS, having a unique ID becomes even more critical. When you are performing an update or change to features, you need to be able to explicitly identify which feature or set of features you are interested in. This also applies to selecting features, whether spatially or within a table. These operations rely on indexes which are generally based on the specified Unique ID, or Primary Key. There are also some data storage formats where the spatial data is stored separately from the attribute data. Linking a feature to its record is accomplished in part using that Unique ID.
With this being said, it is important to draw a distinction between the ObjectID or FeatureID that is assigned by Esri to all tables or layers, and other Unique IDs. While these attributes are Unique IDs, they are not suitable for use as a Primary Key. This is because some database operations may reassign unused values in these sequences. If this were to occur, and this field was used as part of a join between tables, records may be joined incorrectly.
To summarize, UniqueIDs are a critical aspect of any GIS layers. They are used in many ways, from the basics like editing and display of layers, to spatial index creation, to joins and advanced queries. Lastly, the UniqueID should always be something that you designate and control. This way, you will be able to have confidence in the integrity of the UniqueID.