What does "ADD AUTO_INCREMENT value" mean in phpMyAdmin

10,127

Solution 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.

Solution 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

Solution 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"

Share:
10,127
IEnumerable
Author by

IEnumerable

PHP Web developer with some experience in C#, WinForms and ASP.NET. Currently using PyroCMS and codeignighter Languages (PHP, Codeignighter, C#, ASP, CSS3, HTML5 and Javascript, Silverlight)

Updated on September 15, 2022

Comments

  • IEnumerable
    IEnumerable about 1 year

    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?

  • IEnumerable
    IEnumerable about 10 years
    Im copying the DB to another with a new name, not exporting
  • IEnumerable
    IEnumerable about 10 years
    OK so if I wanted to make a replica DB, I should select these two values ?
  • Geoffroy
    Geoffroy about 10 years
    Added links in the post ;)
  • plain jane
    plain jane about 10 years
    provided you copy the tables which are there in the constraint (e.g Foreign Key constraint) in the replicated DB