Answers.Body: <p>I think <code>hook_civicrm_check</code> with the following SQL would handle notifications of duplicates:</p>
  <pre><code>SELECT phone FROM civicrm_phone GROUP BY phone_numeric HAVING count(*) &gt; 1;
  </code></pre>
  <p>For back-end forms, you could modify the Supervised dedupe rules, though this wouldn't catch non-unique numbers belonging to different contact types.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-02-11T17:34:30.363Z
Answers.Text: Using the system status check is an interesting approach. We'd have to tweak that SQL for the exact rules I described, but I get the idea. It's not as immediate as I'd like, though. Ideally I'd like to catch duplicates during import right away.
Answers.UserDisplayName: Noah Miller
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-02-11T15:07:46.737Z
Answers.LastActivityDate: 2021-02-11T15:07:46.737Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>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.</p>
  <p>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.</p>
  <p>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, <a href="https://civicrm.stackexchange.com/q/4258/446">Enforce uniqueness of custom field</a>, 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?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-02-09T20:58:38.150Z
Comments.Text: just to add to the issue, you will need to be either forcing the format on data entry, or ensuring that all spaces brackets and dashes are dropped before doing the comparison with 'phone_numeric' field (which i only discovered when we went in to the rabbit hole of enforcing country prefix if contact was not in the home country. tricky shit it seems.
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-02-12T10:13:06.233Z
Comments.Text: Interesting question ... at the db level, you could add a unique index on `civicrm_phone.phone_numeric` but that would not just be on mobiles and provides no flexibility for exceptions - and failures are unlikely to be presented meaningfully to the user.
Comments.UserDisplayName: Aidan
Tags: <import><forms><workflow><data-model>
Title: Enforcing the uniqueness of fields/entities (e.g. phone numbers)
ThreadText: # Enforcing the uniqueness of fields/entities (e.g. phone numbers)
  
  (Asked by: username_0 on 2021-02-09)
  
  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](https://civicrm.stackexchange.com/q/4258/446), 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?
  
  **Tags:** import, forms, workflow, data-model
  
  > **Comments:**
  > **username_1**: just to add to the issue, you will need to be either forcing the format on data entry, or ensuring that all spaces brackets and dashes are dropped before doing the comparison with 'phone_numeric' field (which i only discovered when we went in to the rabbit hole of enforcing country prefix if contact was not in the home country. tricky shit it seems.  
  > **username_2**: Interesting question ... at the db level, you could add a unique index on `civicrm_phone.phone_numeric` but that would not just be on mobiles and provides no flexibility for exceptions - and failures are unlikely to be presented meaningfully to the user.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_3 on 2021-02-11. Score: 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.
  
  > **Comments:**
  > **username_0**: Using the system status check is an interesting approach. We'd have to tweak that SQL for the exact rules I described, but I get the idea. It's not as immediate as I'd like, though. Ideally I'd like to catch duplicates during import right away.  
  
  ---
  
  