title: MySQL - Create an UNIQUE index on two columns for ON DUPLICATE KEY
body: <p>I have a table with 4 columns. I want to be able to INSERT or UPDATE the value column on creation (I don't care about indexes row ID's).</p>
  
  <pre><code>CREAT TABLE tablename (
   id,   (primary)
   user_id, (index)
   setting_id, (index)
   value  (index)
  );
  </code></pre>
  
  <p>Originally I was going to run a "REPLACE INTO..." query each time I wanted to save a value. But then I <a href="http://code.openark.org/blog/mysql/replace-into-think-twice" rel="nofollow noreferrer">read about the problems</a> and instead chose a INSERT INTO... ON DUPLICATE KEY UPDATE.</p>
  
  <p>The problem is that I don't have single column for the "INSERT INTO... ON DUPLICATE KEY UPDATE" to use to figure out if this is a new row or if there is an existing row that needs to be updated.</p>
  
  <p>So I figured that I would use a UNIQUE key that was a mix of the two columns that made this row unique (</p>
  
  <pre><code>CREATE UNIQUE INDEX index_name ON table(user_id, setting_id);
  </code></pre>
  
  <p>However, I'm not sure how to advance from here. How do I structure my queries to check this new INDEX when trying to figure out a row already exists with these two column values?</p>
  
  <pre><code>INSERT INTO `tablename` (value, user_id, setting_id) VALUES (1,34,15) ON DUPLICATE KEY UPDATE
  </code></pre>
  
  <p>:EDIT: </p>
  
  <p>By removing the ID primary column leaving only (setting_id, user_id, &amp; value) I was able to create a PRIMARY index on (setting_id, user_id) and then the following query worked. <a href="https://stackoverflow.com/questions/1519319/help-with-on-duplicate-key-update-implementation-design/1519420#1519420">more help here</a>.</p>
  
  <pre><code>INSERT INTO tablename (user_id, setting_id, value)
  VALUES (42, 1, 12)
  ON DUPLICATE KEY UPDATE value = 12
  </code></pre>