Enforcing the uniqueness of fields/entities (e.g. phone numbers)

Question

I'd like to ensure that phone numbers of a certain type (let's say Mobile) are unique across our Civi instance. A given contact should be able to have no more than one Mobile number, and no two contacts should have the same Mobile number. We'd still like to take advantage of Civi's existing infrastructure around phone numbers -- in other words, we don't want to use a custom field instead of the phone number entity.

Ideally, the uniqueness rules would be enforced when data comes into Civi. But there are many ways that data comes in: backend forms, contribution pages, event registration forms, import, api, etc. If we wanted to alert users about duplicates during the various GUI-driven workflows, and also handle situations where a user isn't directly controlling the process (e.g. automatic syncs using the api), we'd need to create several different mechanisms.

Has anyone tried to do this, particularly with something like phone numbers which have a many-to-one relationship with contacts? (A question from 2015, Enforce uniqueness of custom field, is related, but I'm asking a broader question. Civi and its ecosystem of extensions have also evolved in the last 5+ years.) Are there best practices for enforcing uniqueness of a field/entity in CiviCRM, in a way that gives users a chance to correct duplicates during data input whenever possible?

Answer 1

I think hook_civicrm_check with the following SQL would handle notifications of duplicates:

SELECT phone FROM civicrm_phone GROUP BY phone_numeric HAVING count(*) > 1;

For back-end forms, you could modify the Supervised dedupe rules, though this wouldn't catch non-unique numbers belonging to different contact types.