Description
See Find, merge, and prevent duplicates based on Field for the overall problem.
In the scope of this Feature, we allow architects to mark some Field as having unique values and enforce this uniqueness constraint when creating new and updating existing entities.
Configuration
We add a new toggle to the Advanced section of Field settings called Require values to be unique, off by default. A user with update(Type) and readEntities(Type) capabilities can try to enable the toggle:
If there are no existing duplicates, the Field is saved and the constraint is being enforced.
If there are existing duplicates, an error is displayed: "Some <Contacts> already have duplicate <Email> values, please merge them and try again".
🦄 Next: Find and manually merge existing duplicates when enabling unique toggle on this step
Extra options
Uniqueness option: ignore case
Ignore leading and trailing spaces
🦄 Ignore http:// and www. (URL)
🦄 Restrict multiple empty values (do not skip empty values)
Enforcing constraint
If someone tries to create or update an entity in a way that violates the uniqueness constraint, they see an error toast:
*Cannot create <Contact>*
<Contact> with such <Email> already exists
The input (e.g. form) doesn't close due to the error, allowing the user to tweak the unique Field's value.
The same error applies to automations, API, and other ways to create/update an entity, including shared Forms.
Next: When user violates uniqueness constraint, suggest navigating to existing entity with the corresponding value.
Notes
Unique flag should be supported in
Uniqueness cannot be enforced for Formulas, Lookups, and integration Fields.
Uniqueness can be enforced for Name.
Before we properly support duplicate handling in import (Update existing entities or skip duplicates via CSV import), the import will fail if any imported entity violates the constraint.
We do not support extremely large Databases with long Field values when the core timeout gets exceeded. In this cases, we can enforce uniqueness manually via :shch: for critical cases of the most important customers.
❓ Can we change options (e.g. case sensitivity) after the uniqueness constraint is enforced?
Limit number of unique Fields to 5 per Database: if an architect tries to create the 6th one, they get an error: "There can be only 5 Fields that require unique values in a given Database".
🦄 Next
References
Airtable: Offers unique field constraints to maintain data integrity in collaborative databases.
Salesforce: Provides unique constraints on fields to prevent duplication in CRM entries.
Notion: Supports unique property settings to avoid duplicate values in databases.
Microsoft Access: Allows setting primary keys and unique constraints for database fields.
HubSpot: