1

Pre-dominantly, everybody uses the int data-type or it's variants tinyint,smallint and mediumint to generate a unique value for the id field of their database table(s).

I have seen so many questions, asking How to start auto-increment from a value (offset) ? When it comes to generating ids for orders table in specific, I feel everybody wants to start off from an offset.

So, in this context, my questions are:

  1. Is it more of a business rule? If so, how does a business rule make sense while generating numbers?

  2. If it's an individual's decision then what could be the possible elements to be considered while deciding to assign id's value from an offset??

  3. Why not start from 1 for all the ids and what is the need to start assigning ids from an offset? What problem does it solve? What are the Pros and Cons of doing so?

Improve this question
1
  • 2
    E.g. you need an order number which is always n digits.
    – dnoeth
    Aug 2, 2016 at 13:20

2 Answers 2

Reset to default
1

There are a variety of reasons why you may want an offset. Please be aware, some of these reasons are not necessarily good DBA practice.

  1. As dnoeth mentioned, your primary key may be used in some other business process such as an order number. In that case, you don't want your customers thinking they are only the 11th order! Being order number 1100011 is much better.
  2. You may have a old system that is in read only mode. It has all your old customers in it with their primary keys, and you want the new system to pick up where it left off.
  3. Same idea, you know you will be importing PKS of a certain value at some point in the future, so you start with ones you know won't cause a collision.

Personally, the only time I offset is to avoid PK collisions. Sure, there was that one time when I was younger that I just though starting at PK ID 10000 was cool, but that was a long time ago.

Really, there are no Cons of doing so. Except for some nominal space issues between data types and potentially losing the ability to assign PKs up to the data type limit, the database doesn't care if it starts at 1 or 1,000,000. It is going to index it all the same.

Improve this answer
3
  • I'm guessing point 1 is not a good practice as you mentioned. I have gone through posts mentioning it's a bad idea to reveal ids used inside the table. I agree with point 2, makes perfect sense. I couldn't imagine a case where point 3 would be useful in terms of a small sized project. And isn't point 3 an extension of point 2? Help me understand.
    – Hari Harker
    Aug 2, 2016 at 16:40
  • Difference between 2 and 3 is that you may not be importing the data into the new system. Both still deal with collisions.
    – Anthony Genovese
    Aug 2, 2016 at 16:42
  • 1 Isn't necessarily BAD practice, I just wouldn't consider it good practice. It just the potential for a large number of headaches down the line.
    – Anthony Genovese
    Aug 2, 2016 at 16:45
1

There are many reasons why you should not trust AUTO_INCREMENT values to follow any pattern. They only guarantee to be unique at any moment in time.

There is one particular situation that could be deadly to your application. If you delete the highest id, then restart mysql, that id may be reused.

See also More caveats

If you want to build ids in a particular pattern, you must take ownership of the id column. And be careful -- ROLLBACK can trip you up. Multiple connections can be an issue.

Back to your question (if I have not scared you off)...

ALTER TABLE foo AUTO_INCREMENT=200;

See also the variables auto_increment_offset and auto_increment_increment. (They are intended for use in multi-Master replication.)

Manual SEQUENCE: There is a nice example in the ref manual. (Search that page for 'sequence'.)

Improve this answer
8
  • In InnoDB, an id generated by AUTO_INCREMENT will always be unique, even if it is not constrained to be PRIMARY or UNIQUE. Yes, you could explicitly insert a dup into an AI column without the constraint.
    – Rick James
    Aug 2, 2016 at 21:35
  • @RickJames : That was some serious information. Thank you. But, I couldn't find a reply for my questions in your answer. I would like to understand the logical and technical needs of using the offset method to devise ids at the moment. Your answer informs me about how to safely generate such values. Can you please append the answer with your thoughts on my questions?
    – Hari Harker
    Aug 3, 2016 at 9:51
  • added a line to answer
    – Rick James
    Aug 3, 2016 at 15:18
  • I went through the link. It had some good information. But the suggestions leaves me in bit of a surprise! For a project I'm working on, I have close to 30 tables for which I have to generate ids for primary key. The user base could scale up in a few months and real soon we might go on for a master-slave. With this in mind, should I really really make 30 tables just to hold the value of auto-incremented primary keys and use replace every-time before I insert a new row with the new primary key?
    – Hari Harker
    Aug 4, 2016 at 10:24
  • I'm using InnoDB engine and I went through, innodb_autoinc_lock_mode . I thinking of setting the lock-mode to 1. If my anticipations is right, there will be no need for bulk inserts in the system I develop. No legacy data is provided and at the very least I wouldn't be importing it after it's in production. Once scaled up, directing writes to master and reads to the many slaves should do a good job and avoid auto-increment problems. Am I right?
    – Hari Harker
    Aug 4, 2016 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.