Error 1064 when creating table in MySQL Workbench

Question

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

Jose Lora · Accepted Answer · 2021-11-14T18:10:38.250

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_id` INT PRIMARY KEY,
    `firstname` VARCHAR(20) NOT NULL,
    `surname` VARCHAR(20) NOT NULL,
    `salary` INT default 0,
    `city` VARCHAR(20),
    UNIQUE(firstname, surname)
);

Also instead of using #, use _id or no you can check the following reference about MySQL Naming Rules

Error 1064 when creating table in MySQL Workbench

1 Answers1