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)
);