I get the error: incorrect table definition

Question

Im using phpmyadmin for the first time and I get this error: incorrect table definition. there can be only one auto column and it must be defined as key. What am I doing wrong? This is my code:

CREATE TABLE `database_reservering`.`formData` ( 
`nameTeacher` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
`nameChild` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `email` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
`age` INT(11) NOT NULL , 
`date` DATE NOT NULL , 
`comment` VARCHAR(300) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
`id` INT(30) UNSIGNED NOT NULL AUTO_INCREMENT , 
 PRIMARY KEY (`nameTeacher`)
 ) ENGINE = InnoDB;

Auto_increment column has to be primary key. You might also want a unique key on nameteacher. — P.Salmon, Dec 14 '20 at 11:19
@Micha . . . What part of the message do you not understand? It seems quite clear: `id` is an auto-increment column but you have declared `nameTeacher` to be the primary key. — Gordon Linoff, Dec 14 '20 at 11:32

score 0 · Answer 1 · answered Dec 14 '20 at 11:19

The actual error message is:

Incorrect table definition; there can be only one auto column and it must be defined as a key

The problem is that you have id as auto-increment, but the primary key is on nameTeacher. This is not allowed. You can change the statement to make id the primary key, and put a unique constraint on nameTeacher. This implements the same logic, but is valid MySQL syntax:

CREATE TABLE `formData` ( 
    ...
    PRIMARY KEY (`id`),
    UNIQUE (`nameTeacher`)
) ENGINE = InnoDB;

Demo on DB Fiddle

score 0 · Answer 2 · answered Dec 14 '20 at 11:33

Change PRIMARY KEY (nameTeacher) to PRIMARY KEY (id)

Full statement:

CREATE TABLE formData ( 
nameTeacher VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
nameChild VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
email VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
age INT(11) NOT NULL , 
`date` DATE NOT NULL , 
`comment` VARCHAR(300) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
id INT UNSIGNED NOT NULL AUTO_INCREMENT , 
PRIMARY KEY (id)) ENGINE = InnoDB;

score 0 · Answer 3 · answered Dec 14 '20 at 11:45

What do you want to achieve when PK is set to the column other than AUTO_INCREMENT?

If you want to have separate independent autoincremented sequence for each nameTeacher value then alter the engine to MyISAM and define PK like (nameTeacher, id):

CREATE TABLE `formData` ( 
    `nameTeacher` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
    `nameChild` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
    `email` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
    `age` INT(11) NOT NULL , `date` DATE NOT NULL , 
    `comment` VARCHAR(300) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
    `id` INT(30) UNSIGNED NOT NULL AUTO_INCREMENT , 
    PRIMARY KEY (`nameTeacher`, `id`)
) ENGINE = MyISAM;

fiddle

See Using AUTO_INCREMENT for details.

I get the error: incorrect table definition

3 Answers3