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

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, Oct 16 '10 at 13:35

Ramon Marco L. Navarro · Accepted Answer · 2010-10-16T13:41:15.063

5

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`)
);

edited Oct 16 '10 at 13:41

answered Oct 16 '10 at 13:34

Ramon Marco L. Navarro

584
1
8
18

int unsigned would probably be better – Jon Black Oct 16 '10 at 13:46

score 2 · Answer 2 · answered Oct 16 '10 at 13:35

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).

answered Oct 16 '10 at 13:35

Larry Lustig

49,320
14
110
160

Yes, but yours is more refined with the opening paren on its own line. – Larry Lustig Oct 16 '10 at 13:39

score 2 · Answer 3 · answered Oct 16 '10 at 13:36

2

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

ref.

answered Oct 16 '10 at 13:36

Mitch Wheat

295,962
43
465
541

score 2 · Answer 4 · answered Oct 16 '10 at 13:37

2

Use primary key in place of primary_key

answered Oct 16 '10 at 13:37

codaddict

445,704
82
492
529

score 1 · Answer 5 · answered Oct 16 '10 at 13:35

1

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

answered Oct 16 '10 at 13:35

Sarfraz

377,238
77
533
578

id int unsigned auto_increment primary key is perfectly valid. not sure why you've specified a display width of 5, why bother with one at all in this instance ? – Jon Black Oct 16 '10 at 13:49

score 1 · Answer 6 · answered Oct 16 '10 at 13:38

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.

Auto increment column

6 Answers6