CODEWITHSUNDEEP
mysql
Kevin Stowe
Kevin Stowe

Reputation: 11

Insert into an auto increment field

i have a table in which i have started the auto increment id at 10000 so that i have 10000 values reserved for manual insertion of values from admins. However when doing an insert into MyTable(ID,Name,Value) VALUES(500,"Test","Test") i do a select and it ignored my ID i gave it and pushes in into the next 10,000 range. Any suggestions on fixing this or what may be wrong? The code above is of course pseudo but i can give a real code example if it doesnt make sense.

Upvotes: 1

Views: 1127

Answers (1)

Michael J.V.
Michael J.V.

Reputation: 5609

Ok, before you get the answer to your question I have to warn you about extremely bad practice you're trying to do there. Don't get me wrong, many have tried to do what you're doing and it's simply not the way things should work.

Your auto_incremented ID is a primary key. Primary key is used to uniquely identify a row in a table. That's it. It has no other special meaning besides that.

So what does that mean for you? It means that your idea that you will "reserve" 1 - 10k for admins is bad. Why is it bad? Because you're tampering with the primary key. You should never decide what the value of primary key should be, that's databases' job for many reasons (consistency for example). The other thing why it's bad is that you have limited someone to only 10k possible entries. On the other hand, how will you calculate what the next entry for admins is? What if you have entered 1, 2, 3, 4 and then you delete entry with ID = 3? What happens then? What's your next in sequence value? 3 or 5?

Having said that, you should probably rethink your strategy. Why not add a field "isAdmin" that will tell you whether an admin posted something or not?

Upvotes: 7

Related Questions