What does "ADD AUTO_INCREMENT value" mean in phpMyAdmin

Question

In phpMyAdmin, I wanted to copy a database to another name so I would effectively have 2 databases exactly the same. One as my oringinal and another for testing on the staging website site that I have.

There are three main options

structure only
structure and data
data only,

I need structure and data, however there are more checkbox options below;

 CREATE DATABASE before copying  //was checked by default
 Add DROP TABLE / DROP VIEW
 Add AUTO_INCREMENT value //was checked by default
 Add constraints
 Switch to copied database 

What does "ADD AUTO_INCREMENT value" and "Add constraints" mean and if I copy structure and data doesnt any fields with AI get copied?

Answer 1

When you export / import a database including structure and data, phpmyadmin restores it in the same state as it was. The same applies when you copy the database.

Add AUTO_INCREMENT value simply set the auto_increment value correctly so you can correctly use the restored database. This is necessary in case phpmyadmin does some kind of bulk insert where the auto_increment is not incremented on each insert. This is also needed to set the correct value, as you can insert then delete some data in your table, so in order to keep the data consistency you have to keep not only the exact ID but also not use an ID which had been used by an old row.

The Add constraints does exactly what it means, i.e. it restores all the constraints on the table in your database. This is also required if you want a complet dump.

For what you are trying to do, maybe a database replication would be better.

You can find information on replication here and here.

Answer 2

If the "Add AUTO_INCREMENT value" and "Add constraints" checkboxes are checked, the new table should have the same next autoindex and constraints like the copied table. All the fields with AI get filled

Answer 3

"Add AUTO_INCREMENT" is keep doing auto increment in new copied database. if we unchecked then it will stop auto increment from that fields. so, if we want maintain auto increment into new database as well then keep checked "Add AUTO_INCREMENT"