Add a catalog number uniqueness requirement at the database level

Documentation Configuration & Installation
,
1

Important: These steps should only be performed by an IT administrator. If you have any questions or if your database is hosted on Specify Cloud, please contact our support team at support@specifysoftware.org.

Reinstating Catalog Number Uniqueness

Starting with the Specify 7.10 update, catalog number uniqueness will no longer be enforced in Specify 6. Instead, it will be managed through the Uniqueness Rules system in Specify 7, rather than at the database level.

Specify databases on version v7.10.0 or later that utilize Specify 6 and depend on catalog number uniqueness can restore this constraint using a SQL statement.

SQL Statement for Collection-Level Constraint

To add a unique constraint called CatalogNumberUnique on the CollectionObject table, which enforces uniqueness for the catalogNumber field within each collection, use the following SQL statement:

ALTER TABLE collectionobject ADD CONSTRAINT CatalogNumberUnique UNIQUE (CollectionID, CatalogNumber);
  • Constraint Name: CatalogNumberUnique identifies the constraint and can be replaced with a preferred name.
  • Scope and Fields: The columns specified in parentheses after UNIQUE determine the fields that define this uniqueness constraint. In this example, CollectionID provides the scope at the collection level, while CatalogNumber is the field designated as unique.

:eight_pointed_star: For more details on fields and scoping, refer to the Sp7 Uniqueness Rules guide.

SQL Statement for Global Constraint

To change the scope of the uniqueness constraint and make CatalogNumber globally unique, modify the SQL statement by removing CollectionID:

ALTER TABLE collectionobject ADD CONSTRAINT CatalogNumberUnique UNIQUE (CatalogNumber);

Removing the Constraint

If you need to remove the uniqueness constraint later, use the following SQL statement:

ALTER TABLE collectionobject DROP CONSTRAINT CatalogNumberUnique;

:warning: Note: If named differently, replace CatalogNumberUnique with the name of the constraint you wish to remove.