Allow forcing uniqueness for any field in schema configuration

[grantfitzsimmons]

My main concern is that these 'numbers' will need to be unique across the collection without turning on auto-numbering most likely. So basically, they should behave like the main 'catalognumber' field. Is a uniqueness check something that can be turned on anywhere for a field?

Requested By: Corinna P at CSIRO

also requested by UMich, New Mexico, New Brunswick Museum, and many more. This is a common request. If an issue for this has already been created, please merge/close this one.

[grantfitzsimmons]

Requested By: Alexis Beck with NHM Geneva

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/require-unique-value/303/11

[melton-jason]

Here is a list of all uniqueness constraints at a database level
(From #2924 (comment))

TableName	IndexName	Unique Columns
accessionagent	Role	Role, AgentID, AccessionID
agentspecialty	AgentID	AgentID, OrderNumber
appraisal	AppraisalNumber	AppraisalNumber
author	ReferenceWorkID	ReferenceWorkID, AgentID
auth_group	name	name
auth_group_permissions	auth_group_permissions_group_id_permission_id_0cd325b0_uniq	group_id, permission_id
auth_permission	auth_permission_content_type_id_codename_01ab375a_uniq	content_type_id, codename
borrowagent	Role	Role, AgentID, BorrowID
collectingevent	DisciplineID	DisciplineID, UniqueIdentifier
collectionobject	CollectionID	CollectionID, UniqueIdentifier
collectionobject	CollectionID_2	CollectionID, CatalogNumber
collector	AgentID	AgentID, CollectingEventID
deaccessionagent	Role	Role, AgentID, DeaccessionID
determinationcitation	ReferenceWorkID	ReferenceWorkID, DeterminationID
determiner	AgentID	AgentID, DeterminationID
disposalagent	Role	Role, AgentID, DisposalID
django_content_type	django_content_type_app_label_model_76bd3d3b_uniq	app_label, model
extractor	AgentID	AgentID, DNASequenceID
fundingagent	AgentID	AgentID, CollectingTripID
giftagent	Role	Role, GiftID, AgentID
groupperson	OrderNumber	OrderNumber, GroupID
loanagent	Role	Role, LoanID, AgentID
locality	DisciplineID	DisciplineID, UniqueIdentifier
localitycitation	ReferenceWorkID	ReferenceWorkID, LocalityID
pcrperson	AgentID	AgentID, DNASequenceID
preparation	CollectionMemberID	CollectionMemberID, BarCode
specifyuser	Name	Name

To maintain compatibility (both with 6 and with migrating from previous versions of 7), these will be 'read-only' uniqueness rules and will not be able to be modified or removed.

⚠️⚠️ Warnining ⚠️⚠️

It must be noted that any custom uniqueness rule will not be enforced in Specify 6.

[grantfitzsimmons]

Geneva would like this as well!

[grantfitzsimmons]

@melton-jason How far along is your work on this project? I remember you talking to me about this in March.

[grantfitzsimmons]

This is the one and only requirement for CSIRO to migrate their collections this fall.

[realVinayak]

Do they want a support for this on the backend? For example, should those constraints be considered for workbench too?

[melton-jason]

Yes, the rules would be supported by the backend (and thus the API and Workbench) too.

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/specify-7-9-3-release-announcement/1499/1