Auto Increment Question

[DeathDisease]

Hello, i'm wondering how to keep a accurate auto increment in MYSQL. For example say I had an ID field "INT" thats auto increment and I have 5 rows:

 

|ID 1| Row 1

|ID 2| Row 2

|ID 3| Row 3

|ID 4| Row 4

|ID 5| Row 5

 

Now say I delete "Row 4":

 

|ID 1| Row 1

|ID 2| Row 2

|ID 3| Row 3

|ID 5| Row 4

 

So my question is how do I keep the ID# with the corresponding row#? Is it possible to do this, and how so? If not is their another way to accomplish this?

[AndyB]

No, it's not possible. Treat auto-increment as if it didn't exist. Do not even think of messing with it. It's an essential part of making database tables relational. Messing with autoincremented values may render your database useless.

 

OK, so what do you really want to do and why?

[artacus]

For the love of Ober! Why does every n00b have the insatiable desire to do this? I don't understand it.

 

We get this question about once a week. Its still a bad idea this week. Feel free to check back next week and see if it makes sense then.

 

Seriously though. This isn't what PK's are for. If you feel the need to store this info (it should be calculated on the fly), add another column like 'sort_order' and use that.

[trq]

You can't. Incrementing fields are not meant to be relied upon for such consistencies. If you need to rely upon consistent id's your more than likely going about something the wrong way.

 

What are you attempting to achieve?

[rofl90]

Hes attempting to get people to insult him? If you're using auto-increment and randomly deleting row theres no point only use auto-increment i you're not planning on deleting rows AND FGS EXPORT YOUR SQL!

[Barand]

how do I keep the ID# with the corresponding row#?

You don't.

 

The physical location of a record is irrelevant. Your application should work even if they are scattered randomly throughout the table

 

ID 5 may stay at row 5 but when you add a new record it could take the place of a deleted one, so you then have

 

|ID 1| Row 1

|ID 2| Row 2

|ID 3| Row 3

|ID 6| Row 4

|ID 5| Row 5

 

If you want the records in ID sequence, use "... ORDER BY ID" in your query.

[artacus]

LOL. So now that everyone has had a chance to weigh in on the subject, how do you change the darn field?

[Barand]

I don't think we're getting through, are we?

 

You Don't Change It.

You Leave It Alone.

Once Allocated It Keeps its Value.

 

There are only so many ways say this.

[jcbarr]

If you want to change that value, then undo the auto-increment setting on the column. If you want a number the corresponds to the row number then create a nother column in order to do that, one that will be static.

[DeathDisease]

LoL alright cool. Still new to this and not sure how to go about things quite yet.

[btherl]

Wow, 8 posts before you posted again

 

Was your question answered?

[DeathDisease]

No. In a HTML table I want to keep each table row numbered in a column to the left of the table data, so that table row one would be #1 and table row 2 would be #2... and so on. So that when a row is deleted or added that it will still be at the right count. If that makes ne sense. 

[AndyB]

Actually what you want is that the first row displayed shows as #1, the second shows as #2 etc. regardless of the record id (physical location in the database).  Just add a loop counter and display its value as you display each record.