Reset ID field number

PHP
1

Hi,

I am developing a web site and have run some examples. The ID started at 1. Now it is approaching 1000. How can I reset this so that it starts at 1.

If this requires deleting all data that is fine.

Thanks,

Matt.

2

To empty the table and return the index to 1 is simple - the SQL is “TRUNCATE table”. I would not recommend changing the auto increment without deleting the existing records.

Also, if the table is referenced with a foreign key anywhere those derived records will have to be deleted or corrected. Don’t do this lightly.

Truncation is fine during testing. Don’t worry about it in production. Computers do not care how big the id numbers are.

3

I can’t agree or disagree because you haven’t posted your database model. If you are sure you have no foreign keys and that you’re not going to corrupt your data integrity then go ahead and change the ID values.

But it sounds like you’re not sure of what you are doing so it appears you are taking a risk messing with the database like this - but that’s your call, especially in the absence of any details on the data structure for your database.

4

yes

And Wedev1958 - I cannot understand the foreign, primary and unique keys. I did spend about an hour reading about it. But things seem to work fine without getting involved with them. Do you disagree?

Matt.

Foreign keys may be important in this case. The autoincremental id from this table is used in other tables as well?
For example, orderID. If you want to eliminate all data in the orders table, then you’ll have to eliminate all data in the orderdetails table as well, otherwise there will be data in the orderdetails table that has an orderID that doesn’t exist anymore, or even worse, that will exist again when your site goes live.

5

r2d2 - where do i run that code? In PHPMyADMIN - whereabouts?

And Wedev1958 - I cannot understand the foreign, primary and unique keys. I did spend about an hour reading about it. But things seem to work fine without getting involved with them. Do you disagree?

Matt.

6

The mysql manual shows you how but if your ID column is an auto increment and/or a primary or unique key you will have to delete earlier data or change their values.

Does your ID have any foreign keys? If so, you will have to cater for those as well if you delete or change ID values.

7

If you want to reset it to 1, you will need to delete all rows, then run:

ALTER TABLE theTableInQuestion AUTO_INCREMENT=1

(Found in the first result to this search: reset auto increment mysql)