JIYIK CN >

In this article, we will learn about MySQL IDENTITY column.

IDENTITY columns in MySQL

While creating a table, we may not have a unique identifier in the database, which makes choosing a primary key challenging. To solve such a problem, we have to manually assign a unique key to each record, which is usually time-consuming.

In Microsoft SQL Server, an IDENTITY column of a table is an auto-incrementing column. The server generates values ​​for the IDENTITY column.

Most of the time, users cannot add values ​​to an IDENTITY column. You can use an IDENTITY column to uniquely identify a row of a table.

grammar:

IDENTITY [( starting_value, increment_value)]

The AUTO_INCREMENT function of MySQL is equivalent to the IDENTITY column in Microsoft SQL Server. In SQL Server, the functionality of IDENTITY is similar to the AUTO_INCREMENT in MySQL.

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

grammar:

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

In this case, the table_name parameter gives the name of the table on which the column should be created. To prevent database errors, you must enter a primary key if your MySQL column is auto-incrementing.

The function in MySQL AUTO_INCREMENTstarts from 1 and if you do not provide any value for the auto increment column, it increments the subsequent numbers by 1 by default.

Consider the following example to help you better understand the previous 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 previous example, we built an Orders table with the fields order_id, product_name, and SKU. We will use AUTO_INCREMENT to automatically generate the order_id.

In the first case, no primary key was specified to determine the problem; however, in the second example, order_id is used as the primary key of the database.

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

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)