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?