I want to make the autoincrementing Primary Key of my table to only be in the ranges 1-12, 101-112, 201-212, etc.
This is because I have a series of repeating items that are 12 in a set and then you get another set, having them indexed in this way by the Primary Kay would be most easy, I think.
Is it possible?
Advertisement
Answer
This is not the right way to approach what you are doing. If you have repeating items in a “set”, then add a column to the table to specify the set. This is the sensible way to handle the data. It makes the structure of the data in the table more explicit.
Then, if you really want to use a primary key encoded with a value, what is so hard about:
select floor((primarykey - 1) / 12) as setvalue
I don’t recommend this, because auto-incremented ids are not guaranteed to be gapless, but it would work under most circumstances.
Finally, if you really want to do what you are proposing you would need a trigger. This seems like a bad reason to write a trigger, because there are better alternatives.