CODEWITHSUNDEEP
mysqlsqldatabase
mathgenius
mathgenius

Reputation: 513

Limit SQL Primary Key to certain ranges

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?

Upvotes: 0

Views: 631

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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.

Upvotes: 4

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726709

Although this is not directly possible, you can make it work by having a primary key composed of two fields - an unrestricted integer designating a group, and another integer restricted to values 1..12 designating a number within a group.

However, this is not a very good approach, because it necessarily gives your primary key some meaning outside your database model. This is bad, because it makes it easy to exploit this meaning in the code, which in turn makes it a lot harder to refactor your database.

Upvotes: 4

Related Questions