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?
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.
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?
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.
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!
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.
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
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
