How to set the starting number for the primary key index in a table

    See more
    • Updated

    Issue

    If a specific starting number is needed, how would I set this number for the primary key index in a table?

    Resolution

    The primary key index is automatically set and auto-incremented for tables. However, in cases where a specific starting number is needed, customers can set the primary key index in a table themselves as part of schema maintenance.

    To set the primary key index, set the AUTO_INCREMENT setting on the table in question with an ALTER TABLE statement. Here is an example: 

    Create your table

    mysql [acquia]> show create table your_table;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| your_table    | CREATE TABLE `your_table` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    Alter your table

    mysql [acquia]> alter table your_table auto_increment=500;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [acquia]> show create table your_table;
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| your_table    | CREATE TABLE `your_table` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    Non-sequential ID numbering

    Even though the AUTO_INCREMENT is set to 500 at this point, the next value to be generated will be 502 (on this specific server). Generating different IDs on the primary and the secondary database servers avoids replication conflicts.

    mysql [acquia]> insert into your_table (a) select 0;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [acquia]> select * from your_table;
+-----+
| a   |
+-----+
| 502 |
+-----+
1 row in set (0.00 sec)

    Additional node ID information

    Why do node IDs increment by more than one?

    Topics

    Related articles