Identity Column in MySQL

In today’s post, we’ll learn about MySQL’s equivalent of an IDENTITY column.

IDENTITY Column in MySQL

When creating a table, we may not have a unique identity in the database, which makes choosing a Primary Key challenging. To address such a problem, we must manually assign unique keys to each record, which is often time-consuming.

In Microsoft SQL Server, a table’s IDENTITY column is a column whose value automatically increases. The server generates the value for an IDENTITY column.

Most of the time, users cannot add values to the IDENTITY columns. The table’s rows can be uniquely identified using the IDENTITY column.

Syntax:

IDENTITY [( starting_value, increment_value)]

MySQL’s AUTO_INCREMENT function is the IDENTITY column’s equivalent in Microsoft SQL Server. In SQL Server, IDENTITY functions similarly to AUTO_INCREMENT in MySQL.

In MySQL, the AUTO_INCREMENT keyword is used to enable auto-increment. AUTO_INCREMENT by default starts at 1 and increments by 1.

Syntax:

CREATE TABLE table_name
(
   column_1 dataType AUTO_INCREMENT PRIMARY KEY,
   column_2 dataType,
);

The table_name parameter, in this case, gives the name of the table on which columns should be created. To prevent a database error, you must input the primary key if your MySQL column is auto-increment.

The AUTO_INCREMENT function in MySQL starts at 1 and increments the following number by 1 by default if you don’t give any values for the auto-increment column.

Consider the following example to help you better understand the prior idea.

CREATE TABLE Orders(
    order_id INT AUTO_INCREMENT,
    product_name VARCHAR(255),
    sku VARCHAR(255)
);
CREATE TABLE Orders(
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    sku VARCHAR(255)
);

In the preceding example, we have built an Orders table with the fields order_id, product_name, and SKU. We will auto-generate order_id using AUTO_INCREMENT.

In the first case, the primary key is not specified to identify the issue; however, in the second example, the order_id is used as the primary key for the database.

Run the above code line in any browser compatible with MySQL. It will display the following outcome:

ERROR 1075 (42000) − Incorrect table definition; there can be only one auto column and it must be defined as a key
Query OK, 0 rows affected (0.59 sec)