title: Fill deleted numbers in consecutive series of numbers and LAST_INSERT_ID
body: <p>First of all I'm an amateur and non-english native speaker, so I would appreciate it if you would have a little patience with me ;)</p>
  
  <p>Trying to do two things here and I'm not sure if I should do two questions about it, but since it's all related in my case, I would like to say it all in one question.</p>
  
  <p>I'm making a sort of accounting software, in theory for my personnal use. I'm using a DB generated auto_increment ID for almost all my objects, but for some specific cases I need a "parallel" more open ID that won't be primary key but could be manipulated by the user(yeah, I've read lots of questions about "you don't need a consecutive Primary Key", and i understand it and agree, but let me remark that this column <strong>won't be the primary key</strong>, lets call it just a "human-not-computer-expert friendly ID") matching these conditions:</p>
  
  <ul>
  <li>The Id should auto increment when no parameters given.</li>
  <li>When a number is given as a parameter that number should be used if not occupied, if occupied throw an exception.</li>
  <li>The user should be asked if he/she wants to fill the missing IDs by <code>DELETE</code>s and whatever other operations, so if the user "say yes", the minimum missing ID should be automatically found and used.</li>
  </ul>
  
  <p>I have no problem with doing this "by hand" in c#, but are there some way to achieve something like this in MySQL directly? I've read in the MySQL documentation that <code>AUTO_INCREMENT</code> does fulfill my first two conditions, but even if it fills missing deleted numbers by default, which I'm not sure of, I don't want it to do that by default, I need the software to ask first, or at least to do it based on a configuration pre established by the user.</p>
  
  <p>Therefore I think I should do it by hand in c#(at least the last part, but i suspect i will be forced to do it entirely), which brings the question about <code>LAST_INSERT_ID</code>.</p>
  
  <p>So, the MYSQL documentation says:</p>
  
  <blockquote>
    <p>If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined. For transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined. For manual ROLLBACK, the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of the ROLLBACK.</p>
  </blockquote>
  
  <p>I understand that <code>LAST_INSERT_ID()</code> is basically useless if the previous <code>INSERT</code> statement fails for whatever reason.</p>
  
  <p>If that's the case, there's no way to retrieve the last inserted ID that ensures a known behaviour when something fails? Something like when <code>INSERT</code> fails returns 0 or a SQL exception? And if there's no other way what is the standard way of doing it(I suppose <code>MAX(Id)</code> won't do it), if something like a standard way exists... or should I just stop trying to do it at one go and do first the updates, check if all went ok, and then do a <code>SELECT LAST_INSERT_ID</code>?</p>
  
  <p>To sum up: </p>
  
  <ul>
  <li>Are there some way to achieve a column of consecutive numbers that fulfill the given conditions in MySQL directly?</li>
  <li>What's with <code>LAST_INSERT_ID</code>? Should I give up and don't use it directly?</li>
  </ul>