title: Mysql 'insert if not exists', preventing 'overlap'
body: <p>My general question seems to be answered by <a href="https://stackoverflow.com/a/3025332/3650835">https://stackoverflow.com/a/3025332/3650835</a>, but I do not fully understand it after reading the MySql docs and am wondering if my solution will work as I expect, and also wonder if LIMIT 1 is needed.</p>
  
  <p><strong>Goal</strong>: to ensure that, for a given user_id, start and end never "overlap". As an example:</p>
  
  <p><strong>test_table</strong></p>
  
  <pre><code>user_id   start   end
  4         1       5
  4         6       13
  4         11      17     --&gt; NOT allowed, bc 11 &lt;= 13
  2         1       9      --&gt; allowed, user_id is different
  </code></pre>
  
  <p><strong>My current solution</strong></p>
  
  <pre><code>/* this should not insert anything, as it would cause an "overlap" of start
  and end, based on row 2 having end = 13 */
  
  INSERT INTO `test_table` (user_id, start, end) 
  SELECT '4', '11', '17' FROM DUAL 
  WHERE NOT EXISTS (SELECT * FROM `test_table` 
        WHERE user_id = '4' AND end &gt;= '11')
  LIMIT 1;
  </code></pre>
  
  <p>Does the <code>WHERE NOT EXISTS</code> section mean "only do this insert...if this following select returns nothing"?</p>
  
  <p>Also, there was the following comment in the linked solution, but I do not understand why based on the MySql docs this would be true. If true, I could remove Limit 1 from my solution:</p>
  
  <blockquote>
    <p>If you use "from dual" on line 2 instead of "from table", then you
    don't need the "limit 1" clause</p>
  </blockquote>
  
  <p>Thanks for your time.</p>
  
  <p>Edit: here is all the sql for testing/setup:</p>
  
  <pre><code>CREATE TABLE `test_table`(
      `id` INT PRIMARY KEY AUTO_INCREMENT,
      `user_id` INT,
      `start` INT,
      `end` INT
  );
  
  
  INSERT INTO `test_table` (user_id, start, end) 
  SELECT '4', '1', '5' FROM DUAL 
  WHERE NOT EXISTS (SELECT * FROM `test_table` 
        WHERE user_id = '4' AND end &gt;= '1')
  LIMIT 1;
  
  INSERT INTO `test_table` (user_id, start, end) 
  SELECT '2', '1', '9' FROM DUAL 
  WHERE NOT EXISTS (SELECT * FROM `test_table` 
        WHERE user_id = '2' AND end &gt;= '1')
  LIMIT 1;
  
  INSERT INTO `test_table` (user_id, start, end) 
  SELECT '4', '6', '13' FROM DUAL 
  WHERE NOT EXISTS (SELECT * FROM `test_table` 
        WHERE user_id = '4' AND end &gt;= '6')
  LIMIT 1;
  
  /* this should not insert anything, as it would cause an "overlap" of start and end */
  INSERT INTO `test_table` (user_id, start, end) 
  SELECT '4', '11', '17' FROM DUAL 
  WHERE NOT EXISTS (SELECT * FROM `test_table` 
        WHERE user_id = '4' AND end &gt;= '11')
  LIMIT 1;
  </code></pre>