Auto increment column

1
votes

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???

6
mysqlsqlmysql-error-1075
When you're asking a question that's specific to MySQL (since most other SQL RDBMS don't use AUTO_INCREMENT) then you should tag your question with the mysql tag. – Paul Tomblin

6 Answers

5
votes

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`)
);
2
votes
 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).

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

ref.

2
votes

Use primary key in place of primary_key

1
votes

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`)
)
1
votes

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.