Is auto-primary-key generation feature of Databases reliable?

Question

I have developed a web application and various clients can store records to that database simultaneously.

I have set the auto-primary-key generation functionality of that database that generates a primarykey automatically (auto-increement) whenever a new record is added to the database.

So my question is Can I rely on this auto-key-generation feature?

Would there be no problem if various users are storing records simultaneously?

Answer 1

Yes, the auto increment primary key is reliable in that a duplicate key will never be generated. You have to be careful how you use it though.

• You should not assume that it will always increase in increments of exactly 1 (or whatever you set it to).
• You should not insert an entry and then without using transactions expect to be able to select max(id) to get the id of the record last inserted.

Answer 2

I'd like to add one more thing to keep in mind when using the auto_inc feature of MySQL:

Imagine you have a table employees that uses auto_inc and (for whatever reason) a second table named former_employees. Let's further pretend that every employee would have an unique ID (therefore the auto_inc) attached to him - and that he won't even lose it due to dismissal or quitting.

For performance reason (let's just imagine the company has several billion employees) your company moves the records of former employees to the homonymous table.

Now here's a snapshot of the two tables (don't mind the small IDs now):

employees                       former_employees
------------------------        ------------------------
id   |  name  |  ...            id   |  name  |  ...
------------------------        ------------------------
27   | Peter  |  ...            29   | Andrew |  ...
28   | Jacko  |  ...            30   | Dennis |  ...
32   | Paula  |  ...            31   | Lenny  |  ...
                                33   | JoDon  |  ...

Notice that former_employees last ID equals 33 and that employees auto_inc counter equals 34 right now.

If you'd shutdown the server at this stage and restart it, employees auto_inc would jump back to 33!!! That's because MySQL doesn't store the auto_inc counter between restarts!

Keep that in mind, though. Regards.

PS: To circumvent this "feature" you would have to trigger stored procedures that look at former_employees last ID and set that if greater.

Note (S.Leske): This applies to InnoDB tables, but not to MyISAM tables. Don't know about other table engines.

Answer 3

The database developers have taken the necessary precautions to ensure that keys are not duplicated. No software is ever free of bugs, of course, but I've never observed a problem with this functionality.