MySQL Workbench Error 1064 when creating table

1849 views mysql
-3

I am trying to use MySQL Workbench 8.0.3 with MariaDB but I get the following error:

Executing:

CREATE TABLE `mydb`.`customer` (

  `ID` INT UNIQUE UNSIGNED NOT NULL AUTO_INCREMENT,

  `Name` VARCHAR(255) NOT NULL,

  `Email` VARCHAR(255) CHARACTER SET 'utf8' NOT NULL,

  `Street` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL,

  `City` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL,

  PRIMARY KEY (`ID`));


ERROR 1064: You have an error in your SQL syntax; check the manual that     
corresponds to your MariaDB server version for the right syntax to use near 'UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(255) NOT NULL,
  `Email` VARC' at line 2
SQL Statement:
CREATE TABLE `mydb`.`customer` (
  `ID` INT UNIQUE UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(255) NOT NULL,
  `Email` VARCHAR(255) CHARACTER SET 'utf8' NOT NULL,
  `Street` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL,
  `City` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL,
  PRIMARY KEY (`ID`))


Operation failed: There was an error while applying the SQL script to the database.

The code you see above has been generated using the "Create Table" Workbench feature.

Your help is very appreciated.

EDIT: Same error when using PHP/mySQLli

answered question

PK is by default Unique; it does not make sense to define unique constraint on a already defined primary key

1 Answer

2

UNSIGNED and INT go together, so this works:

`ID` INT  UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,

This is shorter:

`ID` INT  UNSIGNED AUTO_INCREMENT PRIMARY KEY,

A primary key is already non-NULL and unique. There is no need to declare those properties twice.

posted this

Have an answer?

JD

Please login first before posting an answer.

Ads

Categories