---
title: "How to set the starting number for the primary key index in a table"
date: "2022-02-10T20:32:50+00:00"
summary:
image:
type: "article"
url: "/acquia-cloud-platform/help/93731-how-set-starting-number-primary-key-index-table"
id: "632b125b-322e-46dc-bbe0-0b5c80504b1a"
---

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](/node/92501) 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?](/node/92501)