phpmyadmin Question posted in
The official documentation can be found here.

I get the error: incorrect table definition – Phpmyadmin

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;
Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search