Fill deleted numbers in consecutive series of numbers and LAST_INSERT_ID

Question

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 ;)

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.

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 won't be the primary key, lets call it just a "human-not-computer-expert friendly ID") matching these conditions:

• The Id should auto increment when no parameters given.
• When a number is given as a parameter that number should be used if not occupied, if occupied throw an exception.
• The user should be asked if he/she wants to fill the missing IDs by DELETEs and whatever other operations, so if the user "say yes", the minimum missing ID should be automatically found and used.

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 AUTO_INCREMENT 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.

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 LAST_INSERT_ID.

So, the MYSQL documentation says:

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.

I understand that LAST_INSERT_ID() is basically useless if the previous INSERT statement fails for whatever reason.

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 INSERT fails returns 0 or a SQL exception? And if there's no other way what is the standard way of doing it(I suppose MAX(Id) 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 SELECT LAST_INSERT_ID?

To sum up:

• Are there some way to achieve a column of consecutive numbers that fulfill the given conditions in MySQL directly?
• What's with LAST_INSERT_ID? Should I give up and don't use it directly?

Answer 1

Situation 1, knowing an id that you want inserted into an AUTO_INCREMENT

Honoring that the AI is not a PK as described.

-- drop table a12b;
create table a12b
(   id varchar(100) primary key,
    ai_id int not null AUTO_INCREMENT,
    thing varchar(100) not null,
    key(ai_id)
);

insert a12b (id,thing) values ('a','fish'); -- ai_id=1
insert a12b (id,thing) values ('b','dog'); -- 2
insert a12b (id,thing) values ('b2','cat'); -- 3
delete from a12b where id='b';
insert a12b(id,ai_id,thing) values ('b',2,'dog with spots'); -- 2 ******** right here
insert a12b (id,thing) values ('z','goat'); -- 4

select * from a12b;
+----+-------+----------------+
| id | ai_id | thing          |
+----+-------+----------------+
| a  |     1 | fish           |
| b  |     2 | dog with spots |
| b2 |     3 | cat            |
| z  |     4 | goat           |
+----+-------+----------------+
4 rows in set (0.00 sec)

Situation 2, having a system where you delete rows at some point. And want to fill those explicitly deleted gaps later: See my answer Here

Situation 3 (INNODB has a bunch of gaps sprinkled all over):

This was not part of the question. Perhaps use a left join utilizing a helper table (at least for ints not varchars. But then again we are talking about ints). If you need to spot a gap without knowing, shoot for a left join with a helper table (loaded up with numbers). I know it sounds lame, but helper tables are lean and mean and get the job done. The following would be a helper table: https://stackoverflow.com/a/33666394

INNODB Gap Anomaly

using the above table with 4 rows, continue with:

insert a12b (id,thing) values ('z','goat'); -- oops, problem, failed, but AI is incremented behind the scene
insert a12b (id,thing) values ('z2','goat'); -- 6 (you now have a gap)

data:
+----+-------+----------------+
| id | ai_id | thing          |
+----+-------+----------------+
| a  |     1 | fish           |
| b  |     2 | dog with spots |
| b2 |     3 | cat            |
| z  |     4 | goat           |
| z2 |     6 | goat           |
+----+-------+----------------+

There are a ton of ways to generate gaps. See This and That