Acquia Product Documentation

Related Products

Date Published: February 10, 2022

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)

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.

Additional node ID information¶

Why do node IDs increment by more than one?

Did not find what you were looking for?

If this content did not answer your questions, try searching or contacting our support team for further assistance.

Cloud Platform Support

Loading...