Invalid MySQL Prefix Key

Question

I had a problem creating a table with phpmyadmin which gives me the following error:

# 1089 - Wrong prefix key; the key part used is not a string, the length used is longer than the key part, or the storage mechanism does not support unique prefix keys

This is the request I am making:

CREATE TABLE `b2b`.`users` ( `id` BIGINT NOT NULL AUTO_INCREMENT , `name` VARCHAR(30) NOT NULL , `surnames` VARCHAR(80) NOT NULL , `birthdate` DATE NOT NULL , `drivingdoc` VARCHAR(20) NOT NULL , `acdate` DATE NOT NULL , `countrydoc` VARCHAR(20) NOT NULL , `province` VARCHAR(20) NOT NULL , `locality` VARCHAR(35) NOT NULL , `address` VARCHAR(150) NOT NULL , `number` VARCHAR(20) NOT NULL , `flat` VARCHAR(20) NOT NULL , `door` VARCHAR(20) NOT NULL , `description` VARCHAR(2000) NOT NULL , PRIMARY KEY (`id`(7))) ENGINE = InnoDB; 

Using MariaDB in ubuntu minimal.

Answer 1

The problem is this:

 PRIMARY KEY (`id`(7)) 

You cannot use part of a number as a key; you must use all of this. In addition, specifying the length for numeric types is in the best case useless and in the worst case it hurts.

Change to:

 PRIMARY KEY (`id`) 

Answer 2

The syntax of the main key is what I have ever seen. Try the following:

 CREATE TABLE `b2b`.`users` ( `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, .... ) ENGINE = InnoDB; 

Or if you want

 CREATE TABLE `b2b`.`users` ( `id` BIGINT NOT NULL AUTO_INCREMENT, .... PRIMARY KEY (id) ) ENGINE = InnoDB; 

Answer 3

You have some errors in your sql, so try to find the difference with this sql

  CREATE TABLE `b2b`.`users` ( `id` INT(7) NOT NULL AUTO_INCREMENT , `name` VARCHAR(30) NOT NULL , `surnames` VARCHAR(80) NOT NULL , `birthdate` DATE NOT NULL , `drivingdoc` VARCHAR(20) NOT NULL , `acdate` DATE NOT NULL , `countrydoc` VARCHAR(20) NOT NULL , `province` VARCHAR(20) NOT NULL , `locality` VARCHAR(35) NOT NULL , `address` VARCHAR(150) NOT NULL , `number` VARCHAR(20) NOT NULL , `flat` VARCHAR(20) NOT NULL , `door` VARCHAR(20) NOT NULL , `description` VARCHAR(255) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;