Does Specify somehow “enforces” the one-to-one relationship by always creating a new record in the CollectionObjectAttribute regardless of whether the attribute already exists in the database?
I would like to understand how this works and make sure we won’t have problems when updating attributes because of Collection Objects sharing CollectionObjectAttributeIDs.
Thanks,
Soraya
Specify version: v7.11.4
Please include your Specify 7 System Information in your request, or include the version of Specify 6 you are using.
→
Thanks for your question! Hard to believe that was almost 2 years ago now.
The short answer is that yes, Specify enforces the one-to-one relationship through application logic. A new CollectionObjectAttribute (COA) record will always be fresh alongside a new CollectionObject (CO), and the system never reuses an existing COA across multiple COs during normal operation.
Because each CO gets its own COA, and existing COAs are updated in place (not deleted and recreated), you should not run into issues with data loss or accidental sharing between records.
The only theoretical way a COA could be shared by multiple COs is via direct SQL manipulation or a custom script that bypasses the application API entirely.
If you have access to the database directly, you could run a query like this as a sanity check to see any cases where there may have been a COA shared between two different COs, but this should not be possible unless edits were made manually:
SELECT CollectionObjectAttributeID, COUNT(*) AS CO_Count
FROM collectionobject
WHERE CollectionObjectAttributeID IS NOT NULL
GROUP BY CollectionObjectAttributeID
HAVING COUNT(*) > 1;
If any results appear, those will need to be resolved manually before continuing work in the database!
