Create GUIDs for all records in a table (SQL)

:warning: This action must be run using MariaDB/MySQL tools, it cannot be performed from within Specify.

“GUID” is short for Globally Unique Identifier, there are various species, the pros and cons of the different types have been widely discussed in the collections and standards communities. Specify GUIDs are “UUIDs” and look like this.


See Wikipedia for more info on UUIDs.

In the biocollections community, GUIDs are useful when publishing to data aggregators such as GBIF (Global Biodiversity Information Facility) or iDigBio (Integrated Digitized Biocollections). They provide a persistent and globally unique way to permanently identify specimen records. By ensuring that Collection Object records have a unique and persistent identifier, it becomes much easier to publish, link and track the re-use of your specimen information beyond your collection.

Specify automatically generates GUIDS for new records created in the data tables shown with the check mark, and does not auto-populate the five other tables shown with an “X” which also have GUID fields.

TableName AutoPopulate GUID
Agent :white_check_mark:
Attachment :white_check_mark:
CollectingEvent :white_check_mark:
Collection :white_check_mark:
CollectionObject :white_check_mark:
CollectionObjectProperty :x:
Determination :white_check_mark:
Geography :x:
GeologicTimePeriod :white_check_mark:
Institution :white_check_mark:
Journal :white_check_mark:
LithoStrat :white_check_mark:
Locality :white_check_mark:
MaterialSample :x:
Preparation :white_check_mark:
PreparationProperty :x:
ReferenceWork :white_check_mark:
Taxon :x:

Specify does not generate GUIDs for the Taxon table as collection catalog database are not logical sources of authority of Taxon information. Taxon name and concept records should use GUIDS minted by recognized global name authorities.

Creating and Populating Fields with GUIDs

This SQL command can be run on your database to create GUIDs for tables where a GUID field exists and there is no value currently present.


Alternatively, one can put GUIDs into any table with an available text field of at least 36 characters in length. But note that future new records created in Specify data tables that do not automatically generate GUIDs when created, will not contain GUIDs unless the above script is run again.