Setting up MySQL Auto Increment Primary Key 101: Syntax & Usage Simplified

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.

Table of Contents

• What is MySQL?
  • Key Features of MySQL
• How to set MySQL Auto Increment Primary Key?
  • MySQL Auto Increment Primary Key Example: Inserting Rows
  • MySQL Auto Increment Primary Key Example: Deleting a Row
  • MySQL Auto Increment Primary Key Example: Applying to Non-Numeric Field
  • MySQL Auto Increment Primary Key Example: Alter Table Command
• Conclusion

What is MySQL?

MySQL is a popular Open-Source Relational Database Management System. MySQL data is stored in tables consisting of rows and columns. Introduced in 1995, MySQL is completely developed, distributed, and maintained by Oracle Corporation. Looking over the technical side, MySQL is written in the C and C ++ languages. MySQL is compatible with multiple platforms such as Microsoft Windows, Oracle Solaris, AIX, Symbian, Linux, and macOS. You can use SQL commands to retrieve, change or add data from your MySQL tables. MySQL is also a vital component of the Modern LAMP stack, which consists of a Linux-based Operating System, Apache Web Server, MySQL Database, and PHP for processing.

SQL can also be used to define the table schema or even configure the relationship between the tables in the database. For instance, you can define the MySQL Auto Increment Primary Key field for automatically generating unique sequential numeric values whenever a row of data is inserted. Client-Side Graphical User Interfaces such as MySQL WorkBench, SequelPro, or DBVisualizer can be used to type the SQL commands and the server will respond with the requested information. MySQL also offers a paid Enterprise version which includes premium support services and several extensions that can be installed as Server Plugins.

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.

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:

• MySQL Auto Increment Primary Key Example: Inserting Rows
• MySQL Auto Increment Primary Key Example: Deleting a Row
• MySQL Auto Increment Primary Key Example: Applying to Non-Numeric Field
• MySQL Auto Increment Primary Key Example: Alter Table Command

1) MySQL Auto Increment Primary Key Example: 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.

2) MySQL Auto Increment Primary Key Example: 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) MySQL Auto Increment Primary Key Example: 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) MySQL Auto Increment Primary Key Example: 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

Conclusion

In this article, you have learned how to effectively set the MySQL Auto Increment Primary Key using the Auto Increment Feature. This is an easy approach to automatically generate unique sequential numbers for your Primary Key Field. By default, the starting value for Auto Increment is 1 and will move forward with an increment of 1. You also set user-defined values for the Auto-Increment using the Alter Table command. It is recommended to specify the unsigned constraint on MySQL Auto Increment Primary Keys to avoid having negative numbers.

After you have set the MySQL Auto Increment ID Primary Key field, you can rest assured of automatic generation of unique sequential numbers whenever you insert a row. To get a complete picture of your business health & performance, you need to consolidate data from MySQL and all the other applications used across your business for Marketing, Customer Relationship Management, Accounting, Sales, etc. To achieve this you need to assign a portion of your Engineering Bandwidth to Integrate Data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse or a destination of your choice for further Business Analytics. All of these challenges can be comfortably solved by a Cloud-Based ETL tool such as Hevo Data.  

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 150+ sources such as MySQL to a Data Warehouse or a Destination of your choice to be visualized in a BI Tool. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

If you are using MySQL as your Database Management System and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 150+ sources & BI tools(Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of setting up MySQL Auto Increment Primary Key! Share your thoughts with us in the comments section below.