Using phpmyadmin to restore data with auto_increment

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Using phpmyadmin to restore data with auto_increment

Post by Bill H »

I have a table which contains one field which is an auto_increment integer.
Using phpmyadmin I do a SQL query and obtain 38 records which I export to an external file.
Using some other process I delete those records.

Now I want to use phpmyadmin to reimport those records back into the table and I need those auto_increment integers to have the same values as they had before.

I'm a bit unsure of the usage for the checkbox in the export process "Add AUTO_INCREMENT value." It's checked by default, but I can't quite decipher what it does.

I would simply experiment and see what happens, but I don't actually have any data that I can afford to trash. I can, of course, set up a table and create a bunch of data and run the test. I will do that if need be, but it's quite a bit of effort, and I thought if someone here could give me the answer first.

The alternative, of course, is to save and restore the whole table, but it's pretty big - 47,000+ records.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

Why on earth do you want to do this? Primary keys should never go away unless you want them to go away permanently. If its a matter of just hiding some records, add a boolean field that will indicate if the record is supposed to be deleted or what not, then update the records later.

If you must do this, then you will need to turn off the primary key constraint, lock the table, insert your records, then turn the constraint back on. There are several ways to do this, check out the msql manual.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Well, actually, it was a matter if testing a script. If the script didn't perform as desired I wanted to restore the database and be able to try again. I didn't realize I was attempting some sort of criminal activity.

I was actually thinking that I might need to temporarily change the field definition, eliminating the autoincrement while re-importing the deleted records, I just thought that there might be an easier way to go about it.

All of which does not tell me precisely what the checkbox does. Although I realize I probably should know what it does, I'm actually not certain.
blackbeard
Forum Contributor
Posts: 123
Joined: Thu Aug 03, 2006 6:20 pm

Post by blackbeard »

Before restoring the table, use the truncate table command. It resets the autoincrement field for you.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

blackbeard wrote:Before restoring the table, use the truncate table command. It resets the autoincrement field for you.
WARNING: It also drops all the data.

Obvious to most, but I thought someone ought to mention it just in case.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

During testing, it's standard procedure to destroy all data after the test completes (pass or fail.) Each test will often need a fresh environment to work with to make sure it has the best chance to succeed with specific data.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

This is a minor upgrade to an existing client system that has been in place for several years. Testing with new data would be ideal, but would be a hell of a lot of work, constructing a new database to test a minor change in the procedure. I'm just going to back up the entire database and restore the whole thing if there is a problem.
Post Reply