Answers.Body: <p>My experiments with setting external_identifier column to <em>bin</em> were not successful.  It crashed basic functions of Civi for reasons unclear.</p>
  <p>Better is to add a hash to the data itself, thanks for the idea Demerit. I found this VBA script that does so through Excel: <a href="https://codedocu.com/Office-365/Excel/Templates/Excel-_colon_-Encoding-values-with-HASH-functions-SHA256?2482" rel="nofollow noreferrer">https://codedocu.com/Office-365/Excel/Templates/Excel-_colon_-Encoding-values-with-HASH-functions-SHA256?2482</a>  Note that installing the .NET framework in Windows and enabling .NET as Windows Feature is required.</p>
  <p>Also note that the external_identifier varchar(64) table is limited to 64 characters.  Either you can truncate the hash or alter the Civi table.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-08-13T00:35:02.950Z
Answers.LastActivityDate: 2022-08-13T00:35:02.950Z
Answers.UserDisplayName: CiviTeacher
Answers.Body: <p>You'd want to rebuild triggers if using trigger logging. If you have code that does a SQL join or compare on this field with another you might want to check that still works, but if it did before it still should. However I'd lean towards _bin rather than one of the _cs collations, since that should automatically cast mismatches, whereas _cs might error.</p>
  <p>The main danger isn't bin it's if you have an extension with utf8 and core with utf8mb4 and there's some comparison or join made on the field.</p>
  <hr />
  <p>An alternate idea if you're ok with editing input files before every import: Use a hash of the external id as the external id. This could be simple or complicated, i.e.</p>
  <ul>
  <li>Add an import file column that's the output of md5 or sha256 of the external id. Use that for import. I think md5 would be sufficient here unless you have a ton of rows.</li>
  <li>Add an import file column that's the first, say, 10 chars of the sha256 of the external id plus the actual external id. This would be unique and then also have the real external id visible if that's a need.</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-07-30T17:11:58.253Z
Answers.LastActivityDate: 2022-07-31T01:41:55.830Z
Answers.LastEditDate: 2022-07-31T01:41:55.830Z
Answers.UserDisplayName: Demerit
Body: <p>We're trying to get External Identifier working for an external system that uses case-sensitive IDs (such that external contact &quot;abc123&quot; John Smith is distinct from external contact &quot;ABC123&quot; Alice Brown).</p>
  <p>By design, CiviCRM treats External Identifier as case-insensitive, and then it requires that External Identifier be unique across contacts; it also uses External Identifier as an identifying key on contact import; and probably other things I'm not thinking of at the moment.</p>
  <p>We could, in mysql, alter the external_identifier column to use a case-sensitive collation such as <code>utf8mb4_unicode_bin</code> in order to achieve case-sensitivity in the unique index, and to provide case-sensitive matching in <code>WHERE</code> clauses on that column.</p>
  <p>What kind of a can of worms would we be opening here? Are there likely to be pathways in CiviCRM's PHP execution that would break outright on such changes?</p>
  
Tags: <mysql><external-id><datatablescollation>
Title: Dangerous to force a binary collation on civicrm_contact.external_identifier?
ThreadText: # Dangerous to force a binary collation on civicrm_contact.external_identifier?
  
  (Asked by: username_0 on 2022-07-29)
  
  We're trying to get External Identifier working for an external system that uses case-sensitive IDs (such that external contact "abc123" John Smith is distinct from external contact "ABC123" Alice Brown).
  
  By design, CiviCRM treats External Identifier as case-insensitive, and then it requires that External Identifier be unique across contacts; it also uses External Identifier as an identifying key on contact import; and probably other things I'm not thinking of at the moment.
  
  We could, in mysql, alter the external\_identifier column to use a case-sensitive collation such as `utf8mb4_unicode_bin` in order to achieve case-sensitivity in the unique index, and to provide case-sensitive matching in `WHERE` clauses on that column.
  
  What kind of a can of worms would we be opening here? Are there likely to be pathways in CiviCRM's PHP execution that would break outright on such changes?
  
  **Tags:** mysql, external-id, datatablescollation
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2022-08-13. Score: 2 | Accepted answer
  
  My experiments with setting external\_identifier column to *bin* were not successful. It crashed basic functions of Civi for reasons unclear.
  
  Better is to add a hash to the data itself, thanks for the idea username_2. I found this VBA script that does so through Excel: <https://codedocu.com/Office-365/Excel/Templates/Excel-_colon_-Encoding-values-with-HASH-functions-SHA256?2482> Note that installing the .NET framework in Windows and enabling .NET as Windows Feature is required.
  
  Also note that the external\_identifier varchar(64) table is limited to 64 characters. Either you can truncate the hash or alter the Civi table.
  
  
  ---
  
  ### Answer by username_2 on 2022-07-30. Score: 2
  
  You'd want to rebuild triggers if using trigger logging. If you have code that does a SQL join or compare on this field with another you might want to check that still works, but if it did before it still should. However I'd lean towards \_bin rather than one of the \_cs collations, since that should automatically cast mismatches, whereas \_cs might error.
  
  The main danger isn't bin it's if you have an extension with utf8 and core with utf8mb4 and there's some comparison or join made on the field.
  
  ---
  
  An alternate idea if you're ok with editing input files before every import: Use a hash of the external id as the external id. This could be simple or complicated, i.e.
  
  * Add an import file column that's the output of md5 or sha256 of the external id. Use that for import. I think md5 would be sufficient here unless you have a ton of rows.
  * Add an import file column that's the first, say, 10 chars of the sha256 of the external id plus the actual external id. This would be unique and then also have the real external id visible if that's a need.
  
  
  ---
  
  