Cloud Platform
How to set the starting number for the primary key index in a table
If a specific starting number is needed, how would I set this number for the primary key index in a table?
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:
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)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)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)Important
This type of non-sequential ID numbering is important because if you expect numbers to be generated sequentially, they will not be. However, if you are simply trying to avoid a given set of numbers, then the above method of altering the table will work.
Cloud Platform