Auto increment column

Question

I want to make an AUTO_INCREMENT column in a database table,here is the syntax i write:

create table comments
(
     name varchar(20),
     mail varchar(30),
     comment varchar(100),
     com_no int auto_increment
);

and i get the following error:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

then i made it as a primary key:

create table comments
(
    name varchar(20),
    mail varchar(30),
    comment varchar(100),
    com_no int primary_key auto_increment
);

and i get the following 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 'primary_key auto_increment,name varchar(20),mail varchar(30),comment varchar(100' at line 1

what is wrong???

Answer 1

It is PRIMARY KEY without the underscore.

create table comments
(
    name varchar(20),
    mail varchar(30),
    comment varchar(100),
    com_no int primary key auto_increment
);

or

create table comments
(
    name varchar(20),
    mail varchar(30),
    comment varchar(100),
    com_no int auto_increment,
    primary key(`com_no`)
);

Answer 2

 create table comments(
    name varchar(20), 
    mail varchar(30),
    comment varchar(100),
    com_no int auto_increment,
    PRIMARY KEY (com_no)
  );

(as per on-line MySQL manual).

Answer 3

The proper syntax goes like this for example:

CREATE TABLE `admin` (
    `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `userid` VARCHAR(50) NULL DEFAULT '0',
    `pwd` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)

Answer 4

create table comments
(
    name varchar(20),
    mail varchar(30),
    comment varchar(100),
    com_no int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (com_no)
);

ref.

Answer 5

Use primary key in place of primary_key

Answer 6

In MySQL, there can be only one auto increment column (that is generally known as identity column) and it should be also defined as a unique key. For example:

create table comments
(
  com_no int NOT NULL AUTO_INCREMENT,
  name varchar(20),
  mail varchar(30),
  comment varchar(100),
  PRIMARY KEY (com_no)
);

Please see MySQL auto increment documentation for more details.