Efficiently handling exponentially rising data has always been a challenge for any growing business. MySQL is a simple yet powerful solution that provides a scalable, effective, reliable, and secure Open Source Relational Database Management System. With Standard SQL support, you can quickly query, manipulate & add data to your MySQL Tables.
One of the important tasks while creating a table is setting the Primary Key. The Auto Increment feature allows you to set the MySQL Auto Increment Primary Key field. This automatically generates a sequence of unique numbers whenever a new row of data is inserted into the table.
In this article, you will learn how to effectively set up the MySQL Auto Increment Primary Key field using MySQL’s Auto-Increment feature.
What is MySQL?
MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and manipulating data. It’s known for its reliability, ease of use, and performance, making it a popular choice for web applications, data warehousing, and e-commerce. MySQL supports various platforms and offers features like data security, replication, and backup, making it suitable for both small and large-scale projects.
Key Features of MySQL
MySQL is widely used by organizations like Facebook, Flickr, Twitter, Wikipedia, and YouTube. This is due to the following set of MySQL’s eye-catching features:
- Ease of Operation: With MySQL, you can work with a diverse set of programming languages such as PHP, PERL, C, C ++, and JAVA. It also provides a set of intuitive tools to simplify time-consuming tasks such as Server Management, Reporting, and Data Analysis. Allowing you to work with a broader range of datasets, MySQL offers full support for several Data structures, JSON and Geospatial data, as well as logical, Numeric, Alphanumeric, Date, and Time data types.
- First-rate Performance: MySQL delivers best-in-class query performance across a variety of clustered servers. It provides a fast-loading utility with a separate memory cache and table index partitioning. MySQL can also efficiently handle fluctuating workloads and process large volumes of data at optimal speeds. This allows you to store more than 50 million rows of data in a table with a table’s default file size limit of 4 GB. However, depending on the configuration of the operating system, this can be increased to a theoretical limit of 8 million terabytes (TB).
- Open-Source: MySQL is under the GNU General Public License (GPL), which means it will always be freely available. It provides you the freedom to customize it on Oracle’s OpenSource MySQL codebase based on your needs. Since it is Open-Source software, a large community has grown, regularly enriching MySQL’s documentation and its online support culture.
- Localization: To make it user-friendly, MySQL supports multiple character sets, including latin1 (cp1252), german, big5, ujis, and more. You can also set the error message language that the server provides to the client. The MySQL Server Time can also be dynamically changed with the ability to set specific time zones for individual clients.
- Data Security: MySQL provides you full control over the Data Access Control settings. Robust mechanisms such as Access Privilege Systems and User Account Management allow you to regulate who can view or use your MySQL data. MySQL sets the highest standard with Host-based Verification and Password Encryption.
Hevo Data is a No-code Data Pipeline that offers a fully managed solution to set up Data Integration for 150+ Data Sources (Including 40+ Free sources) and will let you directly load data from sources like MySQL to a Data Warehouse or the Destination of your choice. Hevo also supports MySQL as a Destination for loading Data into it.
Let’s look at some of the salient features of Hevo:
- Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
- Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
- Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
- Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
Sign up here for a 14-Day Free Trial!
How to set MySQL Auto Increment Primary Key?
When creating a table, assigning a column as the primary key is one of the most common tasks as this key uniquely identifies a row in a database. To ensure that it is unique for every row, you can set the MySQL Auto Increment Primary Key field by using the Auto Increment Feature. This allows MySQL to automatically generate unique sequential numeric values whenever a new row is inserted.
It is to be noted that the data type on Auto Increment should be large enough to accommodate the maximum possible number for that column. MySQL supports INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, and BIGINT as data types for Auto Increment fields. If feasible, you can choose the UNSIGNED attribute with the data type to allow for a greater range of values.
You can set the MySQL Auto Increment Primary Key field via the following syntax:
CREATE TABLE table_name
(
column1 datatype NOT NULL AUTO_INCREMENT,
column2 datatype [ NULL | NOT NULL ],
...
);
In this section, you can go through the following MySQL Auto Increment Primary Key Query Examples:
1) Inserting Rows
- Step 1: create a Products table and set the Product ID as the MySQL Auto Increment Primary Key field.
CREATE TABLE products (
product_id int(11) NOT NULL AUTO_INCREMENT,
product_name varchar(150) NOT NULL,
PRIMARY KEY (`product_id`)
);
- Step 2: Insert 2 rows of data in this table and then print the table.
INSERT INTO products(product_name) VALUES("Shoes"),("Shirt"),("Trouser");
SELECT * FROM products;
OUTPUT:
product_id | product_name
1 Shoes
2 Shirt
3 Trouser
Hence, you don’t have to worry about specifying the unique Primary Keys while inserting new rows of data. Also, Auto Increment by default starts from 1 and increments by 1.
Sync Magento via MySQL to MySQL
2) Deleting a row
Now, if you delete a row with Product ID 2 and insert a new row, then the new Product ID generated will still be 4. Thus, MySQL follows the index where the last record was added.
DELETE FROM products WHERE product_id = 2
INSERT INTO products (product_name) VALUES("Jacket");
SELECT * FROM products;
OUTPUT:
product_id | product_name
1 Shoes
3 Trouser
4 Jacket
3) Applying to Non-Numeric Field
Auto-Increment feature can only be applied to numeric fields. If you try creating a Products table and instead of setting the Product ID as the MySQL Auto Increment Primary Key field, you can set Auto Increment to Product Name.
CREATE TABLE products (
product_id int(11) NOT NULL,
product_name varchar(150) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`product_id`)
);
OUTPUT:
The above code will produce an error message given below.
Error Code: 1063. Incorrect column specifier for column 'product_name'
4) Alter Table Command
It often happens that you might want to make a column value Auto Increment after its initial schema has already been created. You can easily achieve this by using the MySQL Alter Table Command in the following steps:
- Step 1: Create a new Product table for this example without setting the MySQL Auto Increment Primary Key field.
CREATE TABLE products (
product_id int(11) NOT NULL,
product_name varchar(150) NOT NULL,
PRIMARY KEY (`product_id`)
);
- Step 2: add some data also in it.
INSERT INTO products VALUES(2000,"Shoes"),(2001,"Shirt"),(2002, "Trouser");
SELECT * FROM products;
OUTPUT:
product_id | product_name
2000 Shoes
2001 Shirt
2002 Trouser
- Step 3: Now, set the Product ID as the MySQL Auto Increment Primary Key field via the Alter Table command and try inserting a new row of data.
ALTER TABLE products MODIFY COLUMN product_id AUTO INCREMENT
INSERT INTO products (product_name) VALUES("Jacket");
OUTPUT:
product_id | product_name
2000 Shoes
2001 Shirt
2002 Trouser
2003 Jacket
- Step 4: You can also set User-Defined Values for the MySQL Auto Increment Primary Key field by using the Alter Table Command.
ALTER TABLE products AUTO_INCREMENT=3000;
INSERT INTO products (product_name) VALUES("Wallet"),(“Cap”);
SELECT * FROM products;
OUTPUT:
product_id | product_name
2000 Shoes
2001 Shirt
2002 Trouser
2003 Jacket
3000 Wallet
3001 Cap
Load your Data from Source to Destination within minutes
No credit card required
Conclusion
In this article, you learned how to set the MySQL Auto Increment Primary Key using the Auto Increment feature, which automatically generates unique sequential numbers for your primary key field, starting at 1 and increasing by 1 for each new entry. You can also use the ALTER TABLE command to set user-defined values for Auto Increment while ensuring to specify the unsigned constraint to prevent negative numbers. Once configured, MySQL will automatically generate these unique identifiers whenever you insert a new row. However, to gain insights into your business’s health and performance, it’s essential to consolidate data from MySQL and other applications like Marketing, CRM, Accounting, and Sales. This integration requires engineering resources to clean, transform, and load the data into a cloud data warehouse for analytics. A cloud-based ETL tool like Hevo Data can effectively address these challenges, streamlining the process of data integration and analysis.
FAQ
Does MySQL auto increment primary key?
Yes, MySQL supports auto-incrementing primary keys. When a column is defined with the AUTO_INCREMENT attribute, MySQL automatically assigns the next sequential integer to the column when a new row is inserted. This is often used for primary keys, ensuring that each row has a unique identifier without manually specifying it during insertion.
How do I change primary key to auto increment in SQL?
To change an existing primary key column to AUTO_INCREMENT in MySQL, you’ll need to modify the column definition. However, the primary key column must be an integer data type to support AUTO_INCREMENT.
Is primary key automatically indexed in MySQL?
Yes, in MySQL, a primary key is automatically indexed. When you define a primary key on a column (or multiple columns), MySQL creates a unique index to enforce the uniqueness constraint. This index helps improve the speed of querying the table by the primary key.
Sanchit Agarwal is an Engineer turned Data Analyst with a passion for data, software architecture and AI. He leverages his diverse technical background and 2+ years of experience to write content. He has penned over 200 articles on data integration and infrastructures, driven by a desire to empower data practitioners with practical solutions for their everyday challenges.
