Error 1064 when creating table in MySQL Workbench

November 14, 2021

I’m trying to create a table in my schema (mydb) called employee, but whenever I try running the script to create the table I get error #1064.

I’m new to SQL so I was looking at examples on how to create tables and went off those examples to create my own until I bumped into this error. Could anyone explain why I am getting this error message please?

MySQL Workbench Community 8.0 Version 8.0.26:

CREATE TABLE 'mydb'.'employee' 
(
    'emp#' INT PRIMARY KEY,
    'firstname' VARCHAR(20) NOT NULL,
    'surname' VARCHAR(20) NOT NULL,
    'salary' INT default 0,
    'city' VARCHAR(20),
    UNIQUE(firstname, surname)
);

Error:

Error Code: 1064. 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 ”mydb’.’employee’ ( ’emp#’ INT Primary key, ‘firstname’ VARCHAR(20) NOT NULL, ‘s’ at line 1

>Solution :

Your issue is the because you are adding ' single quotes in your CREATE TABLE statement, you need to do it like this:

CREATE TABLE mydb.employee 
(
    emp INT PRIMARY KEY,
    firstname VARCHAR(20) NOT NULL,
    surname VARCHAR(20) NOT NULL,
    salary INT default 0,
    city VARCHAR(20),
    UNIQUE(firstname, surname)
);

If you need the quotes in Mysql you need to use the curly single quote like this:

CREATE TABLE `mydb`.`employee` 
(
    `emp#` INT PRIMARY KEY,
    `firstname` VARCHAR(20) NOT NULL,
    `surname` VARCHAR(20) NOT NULL,
    `salary` INT default 0,
    `city` VARCHAR(20),
    UNIQUE(firstname, surname)
);