In MySQL, I have a table, and I want to set the auto_increment
value to 5
instead of 1
. Is this possible and what query statement does this?
John Joe
12.1k16 gold badges68 silver badges129 bronze badges
asked Jun 9, 2009 at 15:01
2
You can use ALTER TABLE
to change the auto_increment initial value:
ALTER TABLE tbl AUTO_INCREMENT = 5;
See the MySQL reference for more details.
answered Jun 9, 2009 at 15:07
8
Yes, you can use the ALTER TABLE t AUTO_INCREMENT = 42
statement. However, you need to be aware that this will cause the rebuilding of your entire table, at least with InnoDB and certain MySQL versions. If you have an already existing dataset with millions of rows, it could take a very long time to complete.
In my experience, it’s better to do the following:
BEGIN WORK;
-- You may also need to add other mandatory columns and values
INSERT INTO t (id) VALUES (42);
ROLLBACK;
In this way, even if you’re rolling back the transaction, MySQL will keep the auto-increment value, and the change will be applied instantly.
You can verify this by issuing a SHOW CREATE TABLE t
statement. You should see:
> SHOW CREATE TABLE t G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
...
) ENGINE=InnoDB AUTO_INCREMENT=43 ...
answered Sep 13, 2012 at 7:08
CosimoCosimo
2,8061 gold badge24 silver badges25 bronze badges
7
How to auto increment by one, starting at 10 in MySQL:
create table foobar(
id INT PRIMARY KEY AUTO_INCREMENT,
moobar VARCHAR(500)
);
ALTER TABLE foobar AUTO_INCREMENT=10;
INSERT INTO foobar(moobar) values ("abc");
INSERT INTO foobar(moobar) values ("def");
INSERT INTO foobar(moobar) values ("xyz");
select * from foobar;
'10', 'abc'
'11', 'def'
'12', 'xyz'
This auto increments the id column by one starting at 10.
Auto increment in MySQL by 5, starting at 10:
drop table foobar
create table foobar(
id INT PRIMARY KEY AUTO_INCREMENT,
moobar VARCHAR(500)
);
SET @@auto_increment_increment=5;
ALTER TABLE foobar AUTO_INCREMENT=10;
INSERT INTO foobar(moobar) values ("abc");
INSERT INTO foobar(moobar) values ("def");
INSERT INTO foobar(moobar) values ("xyz");
select * from foobar;
'11', 'abc'
'16', 'def'
'21', 'xyz'
This auto increments the id column by 5 each time, starting at 10.
answered Aug 18, 2014 at 14:54
Eric LeschinskiEric Leschinski
143k95 gold badges408 silver badges332 bronze badges
You can also do it using phpmyadmin. Just select the table than go to actions. And change the Auto increment below table options. Don’t forget to click on start
answered Sep 25, 2017 at 19:52
North-WindNorth-Wind
1461 silver badge12 bronze badges
1
Procedure to auto fix AUTO_INCREMENT value of table
DROP PROCEDURE IF EXISTS update_auto_increment;
DELIMITER //
CREATE PROCEDURE update_auto_increment (_table VARCHAR(64))
BEGIN
DECLARE _max_stmt VARCHAR(1024);
DECLARE _stmt VARCHAR(1024);
SET @inc := 0;
SET @MAX_SQL := CONCAT('SELECT IFNULL(MAX(`id`), 0) + 1 INTO @inc FROM ', _table);
PREPARE _max_stmt FROM @MAX_SQL;
EXECUTE _max_stmt;
DEALLOCATE PREPARE _max_stmt;
SET @SQL := CONCAT('ALTER TABLE ', _table, ' AUTO_INCREMENT = ', @inc);
PREPARE _stmt FROM @SQL;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt;
END//
DELIMITER ;
CALL update_auto_increment('your_table_name')
answered Aug 29, 2014 at 16:00
If you need this procedure for variable fieldnames instead of id
this might be helpful:
DROP PROCEDURE IF EXISTS update_auto_increment;
DELIMITER //
CREATE PROCEDURE update_auto_increment (_table VARCHAR(128), _fieldname VARCHAR(128))
BEGIN
DECLARE _max_stmt VARCHAR(1024);
DECLARE _stmt VARCHAR(1024);
SET @inc := 0;
SET @MAX_SQL := CONCAT('SELECT IFNULL(MAX(',_fieldname,'), 0) + 1 INTO @inc FROM ', _table);
PREPARE _max_stmt FROM @MAX_SQL;
EXECUTE _max_stmt;
DEALLOCATE PREPARE _max_stmt;
SET @SQL := CONCAT('ALTER TABLE ', _table, ' AUTO_INCREMENT = ', @inc);
PREPARE _stmt FROM @SQL;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt;
END //
DELIMITER ;
CALL update_auto_increment('your_table_name', 'autoincrement_fieldname');
answered Feb 9, 2016 at 11:41
just export the table with data ..
then copy its sql like
CREATE TABLE IF NOT EXISTS `employees` (
`emp_badgenumber` int(20) NOT NULL AUTO_INCREMENT,
`emp_fullname` varchar(100) NOT NULL,
`emp_father_name` varchar(30) NOT NULL,
`emp_mobile` varchar(20) DEFAULT NULL,
`emp_cnic` varchar(20) DEFAULT NULL,
`emp_gender` varchar(10) NOT NULL,
`emp_is_deleted` tinyint(4) DEFAULT '0',
`emp_registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`emp_overtime_allowed` tinyint(4) DEFAULT '1',
PRIMARY KEY (`emp_badgenumber`),
UNIQUE KEY `bagdenumber` (`emp_badgenumber`),
KEY `emp_badgenumber` (`emp_badgenumber`),
KEY `emp_badgenumber_2` (`emp_badgenumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=111121326 ;
now change auto increment value and execute sql.
answered Jun 14, 2014 at 7:40
0
I googled and found this question, but the answer I am really looking for fulfils two criteria:
- using purely MySQL queries
- reset an existing table auto-increment to max(id) + 1
Since I couldn’t find exactly what I want here, I have cobbled the answer from various answers and sharing it here.
Few things to note:
- the table in question is InnoDB
- the table uses the field
id
with type asint
as primary key - the only way to do this purely in MySQL is to use stored procedure
- my images below are using SequelPro as the GUI. You should be able to adapt it based on your preferred MySQL editor
- I have tested this on MySQL Ver 14.14 Distrib 5.5.61, for debian-linux-gnu
Step 1: Create Stored Procedure
create a stored procedure like this:
DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN
SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
PREPARE stmt FROM @get_next_inc;
EXECUTE stmt;
SELECT @next_inc AS result;
DEALLOCATE PREPARE stmt;
set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
PREPARE stmt FROM @alter_statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Then run it.
Before run, it looks like this when you look under Stored Procedures in your database.
When I run, I simply select the stored procedure and press Run Selection
Note: the delimiters part are crucial. Hence if you copy and paste from the top selected answers in this question, they tend not to work for this reason.
After I run, I should see the stored procedure
If you need to change the stored procedure, you need to delete the stored procedure, then select to run again.
Step 2: Call the stored procedure
This time you can simply use normal MySQL queries.
call reset_autoincrement('products');
Originally from my own SQL queries notes in https://simkimsia.com/reset-mysql-autoincrement-to-max-id-plus-1/ and adapted for Stack Overflow.
I googled and found this question, but the answer I am really looking for fulfils two criteria:
- using purely MySQL queries
- reset an existing table auto-increment to max(id) + 1
Since I couldn’t find exactly what I want here, I have cobbled the answer from various answers and sharing it here.
Few things to note:
- the table in question is InnoDB
- the table uses the field
id
with type asint
as primary key - the only way to do this purely in MySQL is to use stored procedure
- my images below are using SequelPro as the GUI. You should be able to adapt it based on your preferred MySQL editor
- I have tested this on MySQL Ver 14.14 Distrib 5.5.61, for debian-linux-gnu
Step 1: Create Stored Procedure
create a stored procedure like this:
DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN
SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
PREPARE stmt FROM @get_next_inc;
EXECUTE stmt;
SELECT @next_inc AS result;
DEALLOCATE PREPARE stmt;
set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
PREPARE stmt FROM @alter_statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Then run it.
Before run, it looks like this when you look under Stored Procedures in your database.
When I run, I simply select the stored procedure and press Run Selection
Note: the delimiters part are crucial. Hence if you copy and paste from the top selected answers in this question, they tend not to work for this reason.
After I run, I should see the stored procedure
If you need to change the stored procedure, you need to delete the stored procedure, then select to run again.
Step 2: Call the stored procedure
This time you can simply use normal MySQL queries.
call reset_autoincrement('products');
Originally from my own SQL queries notes in https://simkimsia.com/reset-mysql-autoincrement-to-max-id-plus-1/ and adapted for Stack Overflow.
Наиболее простой ответ — это использование запроса alter, где вы задаёте новое значение для AUTO_INCREMENT нужной вам таблицы:
ALTER TABLE tablename AUTO_INCREMENT = value; |
Вы можете достичь такого же эффекта изменениями соответствующего столбца вашей таблицы:
ALTER TABLE tablename MODIFY COLUMN ID INT(10) UNSIGNED; ALTER TABLE tablename MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT; |
Понятно, что MySQL не позволит вам установить значение AUTO_INCREMENT меньше, чем существующие уже в таблице значения + 1.
Если требуется сбросить AUTO_INCREMENT до 1, то придется удалить и все существующие записи. Это можно сделать одной командой:
TRUNCATE TABLE tablename; |
Чтобы узнать текущее значение AUTO_INCREMENT, выполнитe запрос к INFORMATION_SCHEMA
SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘databasename’ AND TABLE_NAME = ‘tablename’; |
Написать комментарий
Данная запись опубликована в 03.08.2022 15:45 и размещена в mySQL.
Вы можете перейти в конец страницы и оставить ваш комментарий.
Мало букафф? Читайте есчо !
Курсоры в MySQL
Август 26, 2015 г.
MySQL позволяет использовать курсоры (CURSORs) в хранимых процедурах. Эта конструкция позволяет организовать сложную обработку данных на стороне сервера …
Читать
Скрипт для переименования базы данных в mySQL
Сентябрь 29, 2020 г.
В mySQL нет выражения вроде RENAME DATABASE oldName to newName. Если возникла задача переименовать базу данных, придется выполнить по-этапно: операции создания новой БД, копирования таблиц из старой базы (RENAME TABLE …) и удаление старой базы.
…
Читать
The auto_increment
field must be an index, or be the first column of one. If you try to just drop the primary key, MySQL will complain. You have to delete the auto_increment
property before moving it:
mysql> ALTER TABLE mytable MODIFY id int, DROP PRIMARY KEY, ADD PRIMARY KEY (ticker);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytable;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| exchange_id | int(11) | YES | MUL | NULL | |
| ticker | varchar(32) | NO | PRI | NULL | |
| instrument | varchar(64) | NO | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sector | varchar(255) | YES | | NULL | |
| currency | varchar(32) | YES | | NULL | |
| created_date | datetime | NO | | NULL | |
| last_updated_date | datetime | NO | | NULL | |
+-------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
If you want id
to continue being auto_increment
, you have to do also:
mysql> ALTER TABLE mytable ADD INDEX `id` (id), MODIFY id int auto_increment;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytable;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | MUL | NULL | auto_increment |
| exchange_id | int(11) | YES | MUL | NULL | |
| ticker | varchar(32) | NO | PRI | NULL | |
| instrument | varchar(64) | NO | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sector | varchar(255) | YES | | NULL | |
| currency | varchar(32) | YES | | NULL | |
| created_date | datetime | NO | | NULL | |
| last_updated_date | datetime | NO | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
Please make sure to create if with the right index (multiple, unique) and nullability (null, not null).
Please also note that, depending on the engine used and/or the queries to be performed, a character field for a primary key may be suboptimal.
Description
AUTO_INCREMENT
атрибут может быть использован для создания уникального идентификатора для новых строк. Когда вы вставляете новую запись в таблицу (или при добавлении атрибута AUTO_INCREMENT с оператором ALTER TABLE ), а поле auto_increment имеет значение NULL или DEFAULT (в случае INSERT), значение автоматически увеличивается. Это также относится к 0, если NO_AUTO_VALUE_ON_ZERO SQL_MODE не включен.
AUTO_INCREMENT
Столбцы AUTO_INCREMENT по умолчанию начинаются с 1. Автоматически созданное значение никогда не может быть ниже 0.
В каждой таблице может быть только один столбец AUTO_INCREMENT
. Он должен быть определен как ключ (не обязательно PRIMARY KEY
или UNIQUE
). В некоторых механизмах хранения (включая InnoDB по умолчанию ), если ключ состоит из нескольких столбцов, столбец AUTO_INCREMENT
должен быть первым столбцом. Механизмы хранения, которые позволяют размещать столбец в другом месте, — это Aria , MyISAM , MERGE , Spider , TokuDB , BLACKHOLE , FederatedX и Federated .
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('fox'),('whale'),('ostrich');
SELECT * FROM animals; + | id | name | + | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | fox | | 5 | whale | | 6 | ostrich | +
SERIAL
— это псевдоним для BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
.
CREATE TABLE t (id SERIAL, c CHAR(1)) ENGINE=InnoDB; SHOW CREATE TABLE t G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `c` char(1) DEFAULT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Установка или изменение значения Auto_Increment
Вы можете использовать оператор ALTER TABLE
, чтобы присвоить новое значение параметру таблицы auto_increment
, или установить системную переменную сервера insert_id , чтобы изменить следующее значение AUTO_INCREMENT
, вставленное текущим сеансом.
LAST_INSERT_ID()
может использоваться для просмотра последнегозначения AUTO_INCREMENT
, вставленного текущим сеансом.
ALTER TABLE animals AUTO_INCREMENT=8; INSERT INTO animals (name) VALUES ('aardvark'); SELECT * FROM animals; + | id | name | + | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | fox | | 5 | whale | | 6 | ostrich | | 8 | aardvark | + SET insert_id=12; INSERT INTO animals (name) VALUES ('gorilla'); SELECT * FROM animals; + | id | name | + | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | fox | | 5 | whale | | 6 | ostrich | | 8 | aardvark | | 12 | gorilla | +
InnoDB
До MariaDB 10.2.3 InnoDB использовал счетчик автоинкремента, который хранится в памяти. Когда сервер перезагружается, счетчик повторно инициализируется на максимальное значение, используемое в таблице, что отменяет действие любой опции AUTO_INCREMENT = N в операторах таблицы.
Начиная с MariaDB 10.2.4 , это ограничение было снято, и AUTO_INCREMENT остается постоянным.
См. Также AUTO_INCREMENT Обработка в InnoDB .
Установка эксплицитных значений
Можно указать значение для столбца AUTO_INCREMENT
. Если ключ является первичным или уникальным, значение не должно еще существовать в ключе.
Если новое значение выше текущего максимального значения, значение AUTO_INCREMENT
обновляется, поэтому следующее значение будет выше. Если новое значение меньше текущего максимального значения, значение AUTO_INCREMENT
остается неизменным.
Следующий пример демонстрирует это поведение:
CREATE TABLE t (id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB; INSERT INTO t VALUES (NULL); SELECT id FROM t; + | id | + | 1 | + INSERT INTO t VALUES (10); SELECT id FROM t; + | id | + | 1 | | 10 | + INSERT INTO t VALUES (2); INSERT INTO t VALUES (NULL); SELECT id FROM t; + | id | + | 1 | | 2 | | 10 | | 11 | +
Механизм хранения ARCHIVE не позволяет вставлять значение ниже текущего максимума.
Missing Values
Столбец AUTO_INCREMENT обычно имеет пропущенные значения.Это происходит потому,что если строка удалена,или значение AUTO_INCREMENT явно обновлено,старые значения никогда не используются повторно.Оператор REPLACE также удаляет строку,и ее значение теряется.С помощью InnoDB значения могут быть зарезервированы транзакцией;но если транзакция не удастся (например,из-за ROLLBACK),зарезервированное значение будет потеряно.
Таким образом,значения AUTO_INCREMENT можно использовать для сортировки результатов в хронологическом порядке,а не для создания числовой последовательности.
Replication
Чтобы сделать master-master или Galera безопасными для использования AUTO_INCREMENT
, следует использовать системные переменные auto_increment_increment и auto_increment_offset для генерации уникальных значений для каждого сервера.
ЗАДЕРЖКИ,ЗАКОНОДАТЕЛЬНЫЕ ЦЕННОСТИ и виртуальные столбцы
Генерация значений автоинкремента при добавлении атрибута
CREATE OR REPLACE TABLE t1 (a INT); INSERT t1 VALUES (0),(0),(0); ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY; SELECT * FROM t1; + | a | + | 1 | | 2 | | 3 | +
CREATE OR REPLACE TABLE t1 (a INT); INSERT t1 VALUES (5),(0),(8),(0); ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY; SELECT * FROM t1; + | a | + | 5 | | 6 | | 8 | | 9 | +
Если NO_AUTO_VALUE_ON_ZERO SQL_MODE установлен, нулевые значения не будут увеличиваться автоматически:
SET SQL_MODE='no_auto_value_on_zero'; CREATE OR REPLACE TABLE t1 (a INT); INSERT t1 VALUES (3), (0); ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY; SELECT * FROM t1; + | a | + | 0 | | 3 | +
See Also
- Начало работы с индексами
- Последовательности — альтернатива auto_increment, доступная в MariaDB 10.3
- AUTO_INCREMENT FAQ
- LAST_INSERT_ID()
- Обработка AUTO_INCREMENT в InnoDB
- ЧЕРНОГОЛОВЫЙ и АВТО_ИНКРЕМЕНТ
- UUID_SHORT () — генерирует уникальные идентификаторы
- Генерация идентификаторов-от AUTO_INCREMENT к последовательности (percona.com)
Содержание,воспроизводимое на этом сайте,является собственностью соответствующих владельцев,и это содержание не просматривается заранее компанией MariaDB.Взгляды,информация и мнения,выраженные в этом содержании,не обязательно представляют собой взгляды,информацию и мнения,выраженные MariaDB или любой другой стороной.
Working with auto-increment IDs & primary keys in SQL
20 July 2020
•
11 min read
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.
Contents:
- Intro: auto-incrementing fields and primary keys
- Create a table with auto-incrementing IDs
- Changing increment values
- Insert auto-incremented values for new items
- Secure your DB with UUIDs
- Syntax summary / reference
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-incrementitem_type
: manually entered as a variable stringitem_category
: manually entered as a variable string, tooitem_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 newIDENTITY
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.