Mysql equivalent of MS-Sql server IDENTITY field

Question

I have a sql-server query and i want to run it (or create an equivalent query) on mysql. But currently i'm getting syntax error. Can anyone help me to create a mysql equivalent of the below mentioned sql-server query?

create table Emp(EmpName varchar(20) not null,keyword varchar(20) not null,
DOB datetime not null,Comments text(65535),EmpId int primary key IDENTITY(1,1));

Following is the error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'IDENTITY(1,1))' at line 1

score 1 · Answer 1 · answered Mar 20 '13 at 10:42

CREATE TABLE Emp (
  EmpId     INT AUTO_INCREMENT,
  EmpName   VARCHAR(20) NOT NULL,
  KEYWORD   VARCHAR(20) NOT NULL, 
  DOB       DATETIME    NOT NULL, 
  Comments  TEXT(65535),  PRIMARY KEY (EmpId)
);

You can use auto_increment .By default it will increase by 1.If you want any other increment you can specify your own.

score 0 · Answer 2 · answered Mar 20 '13 at 10:41

0

You can use an auto-increment field

answered Mar 20 '13 at 10:41

Laurent S.

6,816
2
28
40

score 0 · Accepted Answer · answered Mar 20 '13 at 10:42

It should be defined as auto_incremented primary key instead. For example:

CREATE TABLE Emp (
  EmpId     INT AUTO_INCREMENT,
  EmpName   VARCHAR(20) NOT NULL, 
  KEYWORD   VARCHAR(20) NOT NULL,
  DOB       DATETIME    NOT NULL,
  Comments  TEXT(65535),
  PRIMARY KEY (EmpId)
);

Mysql equivalent of MS-Sql server IDENTITY field

3 Answers3