Как изменить auto increment mysql

If you’re dealing with product data (orders, resources, SKUs, apps), chances are you need to work with auto-incrementing IDs, typically as a primary key or part of a composite one. Getting these to work can be pretty annoying, especially when you consider that major SQL dialects – Postgres, MySQL, and MSSQL – all handle syntax very differently.

This guide will walk through how to implement and work with auto-incremented fields in SQL, run through syntax for different SQL dialects, and explain how to handle more secure UUIDs.

Intro: auto-incrementing fields and primary keys

Imagine you’re working with product data for the inventory of a new solar panel manufacturer and distributor. You need to create a unique item_number for each order placed from today on (think: thousands of orders). Item numbers cannot be duplicated in order to distinguish between each item in your inventory. If you’re creating these primary keys as a composite of table column values, your work is likely slow and cumbersome. You don’t have to suffer. There’s a better way.

When you use auto-incremented fields to assign integer values in your databases, you improve your database stability, limit errors caused by changing values, improve performance and uniformity, increase compatibility, and streamline validation. You can then use auto-increment in SQL queries to assign values in the primary key column automatically.

In short: you make your life—and database management—easier. In the example throughout this article, your table will need to include the following fields:

• item_number: generated using auto-increment
• item_type: manually entered as a variable string
• item_category: manually entered as a variable string, too
• item_name: also manually entered as a variable string

These values will be used to reference products in internal databases that will need to be pulled when customers place an order.

Before we jump into syntax and our example, we want to take a minute to talk about why you should use auto-increment in your database (especially for primary keys). (If you just want to get to the point, click here to jump down to the syntax summary.)

Using auto-incrementing IDs in SQL brings with it a lot of benefits, chief among them saving a bit of your sanity. But it also helps you:

• Retrieve data faster and easier because each primary key is a simple, sequential integer rather than a complex mix of other string values.
• Avoid breaking code if other values in your table change as would happen if your primary key was a compound of other column values.
• Ensure the uniqueness of each key within your table without locking or race conditions as auto-incremented IDs automatically avoid repetition and duplication.
• Improve system performance because they’re a compact data type, which allows for highly optimized code paths that avoid skipping between columns to create keys.

Additionally, primary keys can be used across many systems and drivers to support object-to-row mapping and database-system-agnostic operations.

Having said all of that, using an auto-incremented primary key isn’t appropriate for every use case. Read up on this before really jumping in.

Creating a table with auto-incrementing IDs in a SQL query

If you’re starting from scratch, you’ll need to create a table in your database that’s set up to auto-increment its primary keys.

➞ PostgreSQL

When creating an auto-incremented primary key in Postgres, you’ll need to use SERIAL to generate sequential unique IDs. Default start and increment are set to 1.

The base syntax is:

CREATE TABLE TableName (
Column1 DataType SERIAL PRIMARY KEY,
Column2 DataType,
);

When applied to our example inventory data set, table creation looks like:

CREATE TABLE Inventory (
item_number int SERIAL PRIMARY KEY,
item_type varchar(255),
item_category varchar(255),
item_name varchar(255);

This first step is pretty straightforward. Just be sure to mark your item number column as the PRIMARY KEY.

➞ MySQL

Auto-incrementing in MySQL is pretty similar to SQL Server, except you don’t manually include the starting value and integer value. Instead, you use the AUTO_INCREMENT keyword, which has a default start and increment value of 1.

The basic syntax for creating this table in MySQL is:

CREATE TABLE TableName (
COLUMN1 DataType AUTO_INCREMENT,
COLUMN2 DataType,
);

In our example, here’s the table we’d want to create:

CREATE TABLE Inventory (
item_number int AUTO_INCREMENT PRIMARY KEY,
item_type varchar(255),
item_category varchar(255),
item_name varchar(255);

Like Postgres, you need to make sure that you’re using the PRIMARY KEY keyword for the column you want to generate your unique IDs from.

➞ SQL Server

In SQL Server, you’ll use the IDENTITY keyword to set your primary key (or item_number in our use case). By default, the starting value of IDENTITY is 1, and it will increment by 1 with each new entry unless you tell it otherwise.

To start, create a table. The basic syntax:

CREATE TABLE TableName (
Column1 DataType IDENTITY(starting value, increment by),
Column2 DataType,
);

When applied to our inventory test case, table creation will look like:

CREATE TABLE Inventory (
item_number int IDENTITY(1,1) PRIMARY KEY,
item_type varchar(255),
item_category varchar(255),
item_name varchar(255);

Again—one last time—make sure to include PRIMARY KEY next to the SERIAL keyword, so you’re not just generating a useless list of sequential numbers.

Now, once you’ve created the framework of your table, you’ll need to decide if the default starting and increment values make sense for your use case.

Subscribe to the Retool monthly newsletter
Once a month we send out top stories (like this one) along with Retool tutorials, templates, and product releases.

Changing the increment used by auto-increment in a SQL query

Unless you’re building a new app from scratch, chances are you’ll be importing or working within an existing dataset. This means that you’ll need to adjust the starting point of your auto-increment to account for the existing values (and possibly the increment between values depending on naming conventions).

Working within our inventory dataset example, let’s say you were migrating and updating the records from your first product release into a new table before adding new and improved products. For simplicity’s sake, let’s say your company just happens to have released 49 different items in the first go-round, which means you’ll need to start your new values at 50.

Also, for whatever reason (nobody documented it), your predecessors created past item_numbers in increments of five, so you’ll want to keep the vernacular correct and increment by five with each new product added. Thankfully, this change is easy to make in (just about) every DBMS.

➞ PostgreSQL

Postgres is kind of weird here: changing the auto-increment start and increment values in Postgres involves the ALTER keyword and requires you to create a custom SEQUENCE as a separate, single-row table that you’ll then insert into your employee table.

First, create a custom SEQUENCE using:

CREATE SEQUENCE sequencename
start 2
increment 2;

Note: make sure that this sequence name is distinct (i.e. doesn’t share the name of any other tables, sequences, indexes, views, or foreign tables within your existing schema) to avoid conflicts.

In our example, we want item_number to start at 50 and go up by five with each new value. The code for that would look like:

CREATE SEQUENCE item_number_pk
start 50
increment 5;

Then, you insert this record into the inventory table we created earlier and set the item_number value to this new item SEQUENCE like so:

INSERT INTO Inventory
(item_number, item_type, item_category, item_name)
VALUES
(nextval('item_number_pk'), 'Item Type', 'Item Category', 'Item Name');

Each time you add a value to your table (see below), you’ll need to call out that the item_number value is:

nextval('sequencename')

If you’re only looking to change the starting value of your IDs (and not the increment value), you can just use ALTER SEQUENCE and save yourself some of the legwork. The basic syntax for this looks like:

ALTER SEQUENCE project_id_seq RESTART [value];

For our example, this looks like:

ALTER SEQUENCE Inventory RESTART 50;

➞ MySQL

If you’re using MySQL, you can run ALTER TABLE to set new auto-increment starting values. Unfortunately, MySQL doesn’t support changes to the default increment value, so you’re stuck there.

The basic syntax for this looks like:

ALTER TABLE TableName MODIFY COLUMN value DataType AUTO_INCREMENT=50;

Using our inventory table example, your adjustment to the auto-increment start value would be:

ALTER TABLE Inventory MODIFY COLUMN item_number INT AUTO_INCREMENT=50;

After running this code, future item IDs will start at an item_number of 50 and increment by 1.

➞ SQL Server

To change the starting increment value and increment in SQL Server, set your non-default values during table creation. Looking back at our base syntax from the previous section:

CREATE TABLE TableName (
Column1 DataType IDENTITY(starting value, increment by),
Column2 DataType,
);

In our example, where we want item_number to start at 50 and go up by five with each new value, the code would look like:

CREATE TABLE Inventory (
item_number int IDENTITY(100,5) PRIMARY KEY,
item_type varchar(255),
item_category varchar(255),
item_name varchar(255);

If you’re looking to add auto increment to an existing table by changing an existing int column to IDENTITY, SQL Server will fight you. You’ll have to either:

• Add a new column all together with new your auto-incremented primary key, or
• Drop your old int column and then add a new IDENTITY right after

In the second scenario, you’ll want to make sure your new IDENTITY starts at +1 the value of the last id in your dropped column to avoid duplicate primary keys down the road.

To do this, you’d use the following code:

ALTER TABLE Table DROP COLUMN old_id_column
ALTER TABLE Table ADD new_auto_incremented_volumn INT IDENTITY(starting value, increment value)

With our example, this would look like:

ALTER TABLE Inventory DROP COLUMN old_item_number
ALTER TABLE Inventory ADD item_number INT IDENTITY(50, 5)

Regardless of what DBMS you end up using, though, if you’re following along with our example scenario, your output should resemble the following table (MySQL notwithstanding since it doesn’t support non-default increments of 5). We’ll get into adding the values seen below in the next section.

Build internal tools on top of Postgres, MySQL, SQL Server, and dozens of other data sources with Retool: Assemble your app in 30 seconds by dragging and dropping from 50+ pre-built components. Connect to dozens of data integrations and anything with a REST or GraphQL API. Get started for free👉

Adding values with auto-increment

The good news is that the syntax for adding values with auto-increment is the same for 2/3 of the DBs we’re discussing here. As expected, Postgres is the odd duck since it makes you mention your custom sequence as the value for your primary key with each new entry if you’re incrementing by more than 1.

But for the other two, it’s pretty straightforward. The nice part of auto-increment in SQL Server and MySQL is that you can set your primary key start point and forget about it. No need to refer back and manually add in that value when inserting info for the other columns.

To INSERT into your auto-incremented table, use:

INSERT INTO TableName (FieldName1, FieldName2, FieldName3)
VALUES ('Field1Value','Field2Value','Field3Value');

For our example, let’s say you’re adding your new 175 Watt 12 Volt Flexible Monocrystalline Solar Panel (nice, by the way) to the system:

INSERT INTO Inventory (item_type, item_category, item_name)
VALUES ('sp','solar panels','175 Watt 12 Volt Flexible Monocrystalline Solar Panel');

Then a few days later, after a big shipment makes it in, you need to add five more products simultaneously. You can truncate things by doing the following:

INSERT INTO Inventory (item_type, item_category, item_name)
VALUES ('sp','solar panels','200W Monocrystalline Solar Panel 120-cell'), ('cc','charge controllers','40A MPPT Solar Charge Controller'), ('bc','battery chargers','12V 60A DC to DC Battery Charger'), ('bs','battery chargers','20A AC-to-DC LFP Portable Battery Charger
'), ('ms','monitoring screens','Monitoring Screen for Lithium Batteries');

Note how, in the above examples, you don’t have to mention or INSERT values for item_number. That’s because our friendly neighborhood auto-increment is doing that for us.

The output of these new records into your inventory data table should look like this:

Creating external UUIDs for more secure applications

Auto-incremented primary keys are great but not always the right solution. Exposing your primary key externally – whether in a URL when referencing an employee profile or between your database and others – can open your system up to botnets and other vulnerabilities.

You can create more secure DBs (and improve recall speed) by using auto-increment to create your primary keys for internal, private use only and then create a separate UUID for public use by combining your primary key with a second value within your table, such as item_type. Doing this allows you to keep all the benefits of auto-incrementing both primary keys and public IDs while obfuscating your primary keys.

For our example solar panel manufacturer and reseller, our primary key is our auto-incremented unique id item_number, and our public-facing UUID (public_id) is a combination of that item_number and our item_type. This public-facing UUID is what customers will see on each product’s web page, what they can reference for issues and returns, and what will appear on their order invoices, while our primary key (item_number) is hidden and reserved for internal database use only.

To do this, your code—using our example and working with SQL Server as your DBMS—would look this:

CREATE TABLE Inventory (
item_number int IDENTITY(50,5) PRIMARY KEY,
public_id varchar(10),
item_type varchar(255),
item_category varchar(255),
item_name varchar(255);

This would automatically create a unique item_number that starts at 50 and increases by five for each new value, and also create one new column—public_id—with a varchar data type that will serve as one-half of our UUID.

You’d then add in your product information—as we did in previous sections—with a concatenation of your item_number and public_id fields.

INSERT INTO Inventory (item_type, item_category, item_name)
VALUES ('sp','solar panels','175 Watt 12 Volt Flexible Monocrystalline Solar Panel'), ('sp','solar panels','200W Monocrystalline Solar Panel 120-cell'), ('cc','charge controllers','40A MPPT Solar Charge Controller'), ('bc','battery chargers','12V 60A DC to DC Battery Charger'), ('bs','battery chargers','20A AC-to-DC LFP Portable Battery Charger
'), ('ms','monitoring screens','Monitoring Screen for Lithium Batteries');
UPDATE Inventory SET public_id = concat(item_type, item_number);

This code would result in the following output, now with an additional column for your public-facing item IDs.

Once you’ve generated the above fields, you’re now able to reference your product information internally between apps and databases using your item_number primary key, as well as have a universally unique ID-public_id-that can be referenced by customers, accounting, and sales teams without compromising app security.

Syntax summary: auto-incrementing IDs in SQL queries

If you’re just here to CTRL+F your way to a solution (no judgment), you’ve come to the right section. Below is a quick and dirty breakdown of the syntax for each of the concepts discussed at length above.

Creating a table with auto-incrementing IDs in a SQL query

PostgreSQL

CREATE TABLE TableName (
Column1 DataType SERIAL PRIMARY KEY,
Column2 DataType,
);

MySQL

CREATE TABLE TableName (
COLUMN1 DataType AUTO_INCREMENT,
COLUMN2 DataType,
);

SQL Server

CREATE TABLE TableName (
Column1 DataType IDENTITY(starting value, increment by),
Column2 DataType,
);

Changing the increment used by auto-increment in a SQL query

PostgreSQL

CREATE SEQUENCE sequencename
start 2
increment 2;

Then insert this sequence into your main table:

INSERT INTO TableName
(Column1, Column2, Column3, Column4)
VALUES
(nextval('sequencename'), 'Value1', 'Value2', 'Value3');

Or, if you’re just looking to change the starting value of IDs:

ALTER SEQUENCE project_id_seq RESTART [value];

MySQL

ALTER TABLE TableName MODIFY COLUMN value DataType AUTO_INCREMENT=50;

SQL Server

CREATE TABLE TableName (
Column1 DataType IDENTITY(starting value, increment by),
Column2 DataType,
);

Adding values with auto-increment (Postgres, MySQL, SQL Server)

INSERT INTO TableName (FieldName1, FieldName2, FieldName3)
VALUES ('Field1Value','Field2Value','Field3Value');

Using auto-increment in internal and external SQL queries (SQL Server)

CREATE TABLE Inventory (
item_number int IDENTITY(50,5) PRIMARY KEY,
public_id varchar(10),
item_type varchar(255),
item_category varchar(255),
item_name varchar(255);

Then insert your values and update your public-facing, external IDs to your concatenated values:

INSERT INTO TableName (Column1, Column2, Column3)
VALUES ('value1','value2','value3');
UPDATE TableName SET new_public_id = concat(column1, auto-incremented-value);

Make your life easier, use auto-increment in SQL queries

Using auto-increment is often a better way to create and manage values within your databases, no matter what DBMS you use. Doing so streamlines primary ID creation, improves database management by lowering retrieval times, and creates consistency among databases.

If you’re looking for more SQL syntax help across dialects, check out our guide to formatting and dealing with dates in SQL.