4

This may be just me being a stickler for order, but sometimes I start a new project using an old database where I have to delete a bunch of data. Some of that data includes member info. Now, while deleting members, if I add a new one, the member ID assigned to that member is based on the previously added/deleted member. So if the last member was ID 200, the new member is ID 201.

However, if I delete it all down to the original 4 members, Member ID still follows the last one when I add the 5th member!

So it's like, Member ID 1, 2, 3, 4, then 201. Which looks super super odd.

IN SHORT:

If I delete members & add a new one, is it possible to have their member ID to be consecutive to current member IDs, not the deleted?

Improve this question
1
  • This bothers me as well with entry IDs so you're not alone on this one :)
    – Natetronn
    Jan 16, 2013 at 18:43

2 Answers 2

Reset to default
8

What you're referring to is the auto increment value in a table. You can change this by running the following query:

 ALTER TABLE mytable AUTO_INCREMENT = X

...where X is the next number you'd expect for your IDs.

This is a database-level change and not something ExpressionEngine has as a "feature". Only make this change if you understand you might break something.

If I delete members & add a new one, is it possible to have their member ID to be consecutive to current member IDs, not the deleted?

Ultimately, I'd say "get over it" (as a friend, of course). Keeping the IDs in order is not worth the hassle (and potential risk) involved.

Improve this answer
4
  • Need more clarification. Does X = number I want to start it at? e.g. if the last member ID was 4, should I put 4 in X or 5?
    – lealea
    Jan 16, 2013 at 18:39
  • As for potential risk, if it's a new site with little to no entries (ie i'm just setting things up from an old db), would that risk be quite low?
    – lealea
    Jan 16, 2013 at 18:41
  • Yes, X would be the next number you'd be looking for. As for risk, you're correct in saying risk would be low for a site with so little data involved. Brandon touched on that a bit in his response as well.
    – Erik Reagan
    Jan 16, 2013 at 18:43
  • I edited the post to clarify the X in the query.
    – Erik Reagan
    Jan 16, 2013 at 18:50
6

This is because all “id” columns in EE are set to AUTO_INCREMENT in MySQL. Expressionengine is never explicitly choosing what the next member/entry/etc.’s ID is going to be – MySQL assigns it automatically.

MySQL stores the latest auto-increment value internally, and it won’t automatically drop it back when a row is deleted. Some tools like phpMyAdmin will let you reset the auto increment value. But it’s probably not a good idea to mess with it, especially when talking about EE, since other areas of the system might still be referring to that deleted member, via their now nonexistent member_id. (Other systems solve this problem using foreign keys, but EllisLab chose not to use those in ExpressionEngine.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.