[smaboud]

I have an auto incroment field, when i add the first product, it will be set to 1, when i add the 2nd product it will be set to 2 and so on, now imagine that i have 10 added row in my DB,

I have a form to delete the entered items, like

$sql="DELETE FROM t2790 WHERE product_id = '".$q."'";

When I enter a new row after deleting all the inserted rows, the auto increment still fill out the filed with number 11 instead of number 1.

how to update the field value to be like 1 - 2 - 3 - 4 - 5 - ... after deleting for example row number 3?

[hamidkhl]

when you use DELETE, it's like that
you should use TRUNCATE TABLE

TRUNCATE TABLE <table name>

[AdaHacker]

smaboud, on 13 May 2012 - 09:03 AM, said:

how to update the field value to be like 1 - 2 - 3 - 4 - 5 - ... after deleting for example row number 3?

Question: why do you want to do this? Most of the time, the point of auto-incrementing IDs is to supply have a surrogate key because your data doesn't have a natural key. In that case, it doesn't matter what values the keys have - the only thing that's important is that they're unique. So if you're just using the auto-incrementing column as a plain-old primary key, then you don't really need to do this at all. In fact, changing key values like this is almost always a terrible idea - it has the potential to cause problems with performance and data integrity, while offering no real benefits.

hamidkhl, on 13 May 2012 - 11:59 AM, said:

when you use DELETE, it's like that you should use TRUNCATE TABLE

Just to be clear, TRUNCATE TABLE is only useful when you want to clear the entire table (it's roughly equivalent to dropping and re-creating the table), which doesn't quite seem to be what the OP is asking for.

[smaboud]

Hi,

Well The auto increment field is my row number and my item number. I also have 2 foreign keys, USER ID and ORDER ID, so that I need the row numbers be in order, that's why I made them to be auto increment. I also use the auto increment field as my dynamically generated row(index) so the user can edit or delete them.

as you see auto incremented field is doing 3 jobs, that's why I have to make them to be in order.

I just need to know how to regenerate them again once a user deleted one row

[rgfirefly24]

It is a bad idea to have something have multiple jobs. The auto-increment ID should be used only as a surrogate key. If you have a need for data be in sequential order, and to reuse deleted ID's then I would suggest using a different column. Point is, don't mess with the Auto-Incremented ID column.

[e_i_pi]

As AdaHacker and rgfirefly24 have already stated, auto-incremented IDs in MySQL (which I assume you're using) are intended as unique identifiers only. They should not be used to calculate row numbers, display orders, or any other purpose that is achievable by code. The problems you are having are due to the fact that you are misusing auto-increments.

If you need to find out the last insert ID, so that you can write a row to a different table, you use a last insert id method. If you start trying to reset the auto-increment, so that there are no gaps in your IDs, then you will run into major problems to do with race conditions.

My work has attempted to do something similar to what you are attempting. It is folly, and it is a failure. It does not work, and it causes problems the moment one bad piece of code runs against your DB.

Another major problem (like there isn't enough) with changing the ID (read: PK/surroate key) in this way is that, unless you have tight cascade/delete constraints, you are going to run into data integrity issues that will be irreversible.

[BobRodes]

smaboud, on 13 May 2012 - 07:41 PM, said:

Hi,

Well The auto increment field is my row number and my item number.

It shouldn't be. It should serve one purpose, which is to provide a primary key that can be plugged into tables that reference the record.

Quote

It also have 2 foreign keys, USER ID and ORDER ID, so that I need the row numbers be in order, that's why I made them to be auto increment.

If that's why you made them to be auto increment, you don't understand auto increment. There should be no assumptions made as to the value in the field, except that it is unique and doesn't change. Let me ask you this: if you change the value in the auto increment field, do you also plan to change the foreign key values to match?