- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How to set initial value and auto increment in MySQL?
The AUTO_INCREMENT attribute is used to generate a unique identify for new rows. If a column is declared as ‘NOT NULL’, it is possible to assign NULL to that column to generate a sequence of numbers.
When any value is inserted into an AUTO_INCREMENT column, the column gets set to that value, and the sequence also gets reset so that it generates values automatically, in the sequential range from largest column value.
An existing ‘AUTO_INCREMENT’ column can be updated that will reset the ‘AUTO_INCREMENT’ sequence as well. The most recent auto-generated ‘AUTO_INCREMENT; value can be retrieved using the ‘LAST_INSERT_ID()’ function in SQL or using the ‘mysql_insert_id()’ which is a C API function.
These functions are connection-specific, which means their return values are not affected by other connections which perform the insert operations. The smallest integer data type for ‘AUTO_INCREMENT’ column can be used, which would be large enough to hold the maximum sequence value which is required by the user.
Rules for AUTO_INCREMENT
The following rules need to be followed while using the AUTO_INCREMENT attribute −
Every table has only one AUTO_INCREMENT column whose data type would be an integer typically.
The AUTO_INCREMENT column needs to be indexed. This means it can either be a PRIMARY KEY or a UNIQUE index.
The AUTO_INCREMENT column must have a NOT NULL constraint on it.
When the AUTO_INCREMENT attribute is set to a column, MySQL automatically adds the NOT NULL constraint to the column on its own.
If an id column hasn’t been added to the table, the below statement can be used −
ALTER TABLE tableName ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (id);
If an id column is already present, then the below command can be used −
ALTER TABLE tableName AUTO_INCREMENT=specificValue;
Here, tableName refers to the name of the table fr which the ‘AUTO_INCREMENT’ column needs to be set. The ‘specificValue’ refers to an integer from where the ‘AUTO_INCREMENT’ values are specified by the user to begin.
- Related Articles
- Set auto increment initial value for MySQL table using ALTER command
- Truncate a MySQL table and then set a custom value to auto increment
- MySQL query to set my auto increment column ( id ) to zero or reset the value of auto increment field?
- How can I set my auto-increment value to begin from 1 in MySQL?
- How to auto-increment value of tables to lower value in MySQL?
- How to set the initial value of an auto-incremented column in MySQL using JDBC?
- Set custom Auto Increment with ZEROFILL in MySQL
- How to change auto increment number in MySQL?
- What if I forgot to set Auto Increment? Can I set it later in MySQL?
- Set MySQL int column to auto increment by 1 beginning at 10000?
- Changing the current count of an Auto Increment value in MySQL?
- How to get the next auto-increment id in MySQL?
- How to make MySQL table primary key auto increment?
- Passing NULL to MySQL for auto increment?
- How to Auto-Increment Cell Value in Excel after Each Printing?