title: Use accent senstive primary key in MySQL
body: <h2>Desired result :</h2>
  
  <p>Have an accent sensitive primary key in MySQL.</p>
  
  <p>I have a table of unique words, so I use the word itself as a primary key (by the way if someone can give me an advice about it, I have no idea if it's a good design/practice or not). </p>
  
  <p>I need that field to be accent (and why not case) sensitive, because it must distinguish between, for instance, <code>'demandé'</code> and <code>'demande'</code>, two different inflexions of the French verb "demander". I do not have any problem to store accented words in the database. I just can't insert two accented characters strings that are identical when unaccented.</p>
  
  <h2>Error :</h2>
  
  <p>When trying to create the <code>'demandé'</code> row with the following query:</p>
  
  <pre><code>INSERT INTO `corpus`.`token` (`name_token`) VALUES ('demandé');
  </code></pre>
  
  <p>I got this error :</p>
  
  <pre><code>ERROR 1062: 1062: Duplicate entry 'demandé' for key 'PRIMARY'
  </code></pre>
  
  <h2>Questions :</h2>
  
  <ul>
  <li>Where in the process should a make a modification in order to have two different unique primary keys for "demande" and "demandé" in that table ?</li>
  </ul>
  
  <p><strong>SOLUTION</strong> <a href="https://stackoverflow.com/a/34388168/5419761">using 'collate utf8_general_ci'</a> in table declaration</p>
  
  <ul>
  <li>How can i make accent sensitive queries ? Is the following the right way :
  <code>SELECT * FROM corpus.token WHERE name_token = 'demandé' COLLATE utf8_bin</code> </li>
  </ul>
  
  <p><strong>SOLUTION</strong> <a href="https://stackoverflow.com/a/34388015/5419761">using 'collate utf8_bin'</a> with WHERE statement</p>
  
  <ul>
  <li><p>I found that i can achieve this point by using the <code>BINARY</code> Keyword (see this <a href="http://www.sqlfiddle.com/#!9/99787/1" rel="noreferrer">sqlFiddle</a>). What is the difference between <code>collate</code> and <code>binary</code>?</p></li>
  <li><p>Can I preserve other tables from any changes ? (I'll have to rebuild that table anyway, because it's kind of messy)</p></li>
  <li><p>I'm not very comfortable with encoding in MySQL. I don't have any problem yet with encoding in that database (and I'm kind of lucky because my data might not always use the same encoding... and there is not much I can do about it). I have a feeling that any modification regarding to that "accent sensitive" issue might create some encoding issue with other queries or data integrity. Am I right to be concerned? </p></li>
  </ul>
  
  <h2>Step by step :</h2>
  
  <p>Database creation :</p>
  
  <pre><code>CREATE DATABASE corpus DEFAULT CHARACTER SET utf8;
  </code></pre>
  
  <p>Table of unique words :</p>
  
  <pre><code>CREATE TABLE token (name_token VARCHAR(50), freq INTEGER, CONSTRAINT pk_token PRIMARY KEY (name_token))
  </code></pre>
  
  <p>Queries</p>
  
  <pre><code>SELECT * FROM corpus.token WHERE name_token = 'demande';
  SELECT * FROM corpus.token WHERE name_token = 'demandé';
  </code></pre>
  
  <p>both returns the same row:</p>
  
  <pre><code>demande
  </code></pre>