Equivalent of SQL Server IDENTITY Column in MySQL?

Equivalent of Microsoft SQL Server IDENTITY column in MySQL is AUTO_INCREMENT. The IDENTITY in SQL Server acts like AUTO_INCREMENT in MySQL.

The syntax is as follows −

CREATE TABLE yourTableName
(
   yourColumnName1 dataType NOT NULL AUTO_INCREMENT,
   yourColumnName2 dataType,
   .
   .
   .
   N,
   PRIMARY KEY(yourColumnName1)
);

In MySQL, you need to use primary key if your column is auto_increment otherwise MySQL will give an error. Look at the error −

mysql> create table EquivalentOfIdentityInMySQL
   -> (
   -> ProductId int NOT NULL AUTO_INCREMENT,
   -> ProductName varchar(30)
   -> );
ERROR 1075 (42000) − Incorrect table definition; there can be only one auto column and it
must be defined as a key

To remove the above error, you need to make ProductId as primary key. The declaration of MySQL AUTO_INCREMENT is as follows with ProductId set as primary key −

mysql> create table EquivalentOfIdentityInMySQL
   -> (
   -> ProductId int NOT NULL AUTO_INCREMENT,
   -> ProductName varchar(30)
   -> ,
   -> PRIMARY KEY(ProductId)
   -> );
Query OK, 0 rows affected (0.59 sec)

If you won’t pass any value for the column ProductId, MySQL begins auto_increment from 1 and the next number increments by 1 by default. 

To check the ProductId column, let us insert some records in the table using insert command. The query is as follows −

mysql> insert into EquivalentOfIdentityInMySQL(ProductName) values('Product-1');
Query OK, 1 row affected (0.14 sec)

mysql> insert into EquivalentOfIdentityInMySQL(ProductName) values('Product-2');
Query OK, 1 row affected (0.14 sec)

mysql> insert into EquivalentOfIdentityInMySQL(ProductName) values('Product-34');
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from EquivalentOfIdentityInMySQL;

The following is the output −

+-----------+-------------+
| ProductId | ProductName |
+-----------+-------------+
|         1 | Product-1   |
|         2 | Product-2   |
|         3 | Product-34  |
+-----------+-------------+
3 rows in set (0.00 sec)

Look at the column ProductId, we did not pass any value for this column but we are getting the number beginning from 1 and next number increments by 1.