# Autoincrement **URL:** \ **Category:** Rails\ **Created:** [September 23, 2009, 7:58pm UTC](https://www.ruby-forum.com/t/autoincrement/176027 "2009-09-23T19:58:42Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![djames](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7ab992/32.png) [@djames](https://www.ruby-forum.com/u/djames)\ **Post date:** [September 23, 2009, 7:58pm UTC](https://www.ruby-forum.com/t/autoincrement/176027/1 "2009-09-23T19:58:42Z")
I am using SQLite 3. I have deleted all of the rows of a member table but one. The one row that remains is for the administrative user and has an id of 0. I’d sure like to zero out the autoincrement counter. The documentation for SQLite contains the following warning: The content of the SQLITE\_SEQUENCE table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes. OK. I have been warned. It certainly seems like an easy procedure. The thing about perturbing, “the AUTOINCREMENT key generation algorithm” scares me. Do I really need to be concerned? Is there a better way to accomplish this objective? Thanks for any input. ``` ... doug ``` ---
**Author:** ![djames](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7ab992/32.png) [@djames](https://www.ruby-forum.com/u/djames)\ **Post date:** [September 23, 2009, 8:31pm UTC](https://www.ruby-forum.com/t/autoincrement/176027/2 "2009-09-23T20:31:28Z")
Doug J. wrote: > > Can you afford to make a copy of the SQLite file and -try-… And then > > report back to us? 😉 > > I am pleased to report that it seems to work just fine. The warning > still scares me. I’m afraid that I am going to get unexpectedly > bitten some day. The warning is expressly directed to those who do > not know what they’re doing and I certainly fall into that camp! 🙂 > > ``` > ... doug > > ``` As a very wild guess, if you are autoincrementing things that aren’t plain numbers, the generation algorithm is a little trickier than just “Oh … +1 !”. But again - Me no SQLite expert. ---
**Author:** ![djames](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7ab992/32.png) [@djames](https://www.ruby-forum.com/u/djames)\ **Post date:** [September 23, 2009, 8:01pm UTC](https://www.ruby-forum.com/t/autoincrement/176027/3 "2009-09-23T20:01:13Z")
Well, I am not a SQLite developer, but now I am -very- curious as to what that would do. Can you afford to make a copy of the SQLite file and -try-… And then report back to us? 😉 ---
**Author:** ![djames](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7ab992/32.png) [@djames](https://www.ruby-forum.com/u/djames)\ **Post date:** [September 23, 2009, 9:14pm UTC](https://www.ruby-forum.com/t/autoincrement/176027/4 "2009-09-23T21:14:49Z")
2009/9/23 doug [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > undertake such changes. > > OK. Â I have been warned. Â It certainly seems like an easy procedure. > The thing about perturbing, “the AUTOINCREMENT key generation > algorithm” scares me. Â Do I really need to be concerned? Â Is there a > better way to accomplish this objective? I think that as a matter of principle it is considered bad form to assign any significance to the id field. If you need users to have a number that means something you could have a separate column for this purpose. For the admin user it might be better to have a role field which indicates this. Using an id of 0 means that you can only ever have one admin and can never change the admin user to somebody else. (You could change the name of the admin user to A N Other, but you could not make an existing user (who may have other related data in the db) be the administrator, if I am explaining myself well enough. It may seem like inefficiency and extra work, but in the long run you may agree. I made this mistake myself and had to rework it later. Colin Colin ---
**Author:** ![djames](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7ab992/32.png) [@djames](https://www.ruby-forum.com/u/djames)\ **Post date:** [September 23, 2009, 8:27pm UTC](https://www.ruby-forum.com/t/autoincrement/176027/5 "2009-09-23T20:27:58Z")
> Can you afford to make a copy of the SQLite file and -try-… And then > report back to us? 😉 I am pleased to report that it seems to work just fine. The warning still scares me. I’m afraid that I am going to get unexpectedly bitten some day. The warning is expressly directed to those who do not know what they’re doing and I certainly fall into that camp! 🙂 ``` ... doug ``` ---
**Author:** ![djames](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7ab992/32.png) [@djames](https://www.ruby-forum.com/u/djames)\ **Post date:** [September 24, 2009, 6:29pm UTC](https://www.ruby-forum.com/t/autoincrement/176027/6 "2009-09-24T18:29:22Z")
Doug J. wrote: > > I think that as a matter of principle it is considered bad form to > > assign any significance to the id field. �If you need users to have a > > number that means something you could have a separate column for this > > purpose. �For the admin user it might be better to have a role field > > which indicates this. �Using an id of 0 means that you can only ever > > have one admin and can never change the admin user to somebody else. > > (You could change the name of the admin user to A N Other, but you > > could not make an existing user (who may have other related data in > > the db) be the administrator, if I am explaining myself well enough. > > Good points. I see it as being a judgement call. I originally > started by adopting the convention that the administrative user would > have the login, ‘admin’. Then, I thought that it would be nice to > allow anyone to be the administrator. Then users should have a field indicating their role. It is inappropriate to use the primary key for this. […] > Requiring that the single administrative user have the 0 id > doesn’t bother me. It should. This sort of thing is never a good idea. > However, as I say, it’s a judgement call and your > points are well taken. Thanks for the input. > > ``` > ... doug > > ``` It’s not a judgement call. You’re doing something that is known to be bad practice. Don’t do it. Ever. ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![djames](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7ab992/32.png) [@djames](https://www.ruby-forum.com/u/djames)\ **Post date:** [September 24, 2009, 5:53pm UTC](https://www.ruby-forum.com/t/autoincrement/176027/7 "2009-09-24T17:53:34Z")
> I think that as a matter of principle it is considered bad form to > assign any significance to the id field. If you need users to have a > number that means something you could have a separate column for this > purpose. For the admin user it might be better to have a role field > which indicates this. Using an id of 0 means that you can only ever > have one admin and can never change the admin user to somebody else. > (You could change the name of the admin user to A N Other, but you > could not make an existing user (who may have other related data in > the db) be the administrator, if I am explaining myself well enough. Good points. I see it as being a judgement call. I originally started by adopting the convention that the administrative user would have the login, ‘admin’. Then, I thought that it would be nice to allow anyone to be the administrator. I also preferred comparing integers rather than strings any time that I needed to test whether a particular user was the administrative user. I also think that I was probably influenced by the fact that Unix assigns the 0 id to the root user. Requiring that the single administrative user have the 0 id doesn’t bother me. However, as I say, it’s a judgement call and your points are well taken. Thanks for the input. ``` ... doug ```