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.