MySQL giving me syntax error referring to characters that don't exist?-CodePudding

DROP DATABASE IF EXISTS `company_db`;
CREATE DATABASE `company_db`;
USE `company_db`;

CREATE TABLE department(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    department_name VARCHAR (30) NOT NULL;
);

CREATE TABLE roles(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR (30) NOT NULL,
    salary DECIMAL,
    department_id INT;
);

CREATE TABLE employee(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    first_name VARCHAR (30) NOT NULL,
    last_name VARCHAR (30) NOT NULL,
    role_id INT NOT NULL,
    manager_id INT;
);

Here's my code above. When I try to run this schema file I get the following errors.

ERROR 1064 (42000): 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 '' at line 3
ERROR 1064 (42000): 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 ')' at line 1
ERROR 1064 (42000): 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 '' at line 5
ERROR 1064 (42000): 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 ')' at line 1
ERROR 1064 (42000): 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 '' at line 6
ERROR 1064 (42000): 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 ')' at line 1

What are they talking about? There isn't even a ')' in line 1. Is there something I'm misunderstanding about this? Thanks in advance!

CodePudding user response：

You have an abundance of ;. This terminates each statement, it should not be present within the create:

CREATE TABLE department(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    department_name VARCHAR (30) NOT NULL
);

CREATE TABLE roles(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR (30) NOT NULL,
    salary DECIMAL,
    department_id INT
);

CREATE TABLE employee(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    first_name VARCHAR (30) NOT NULL,
    last_name VARCHAR (30) NOT NULL,
    role_id INT NOT NULL,
    manager_id INT
);

CodePudding user response：

In each create statement, you have “;” after the last column… this is interpreted as the end of the SQL statement which is causing a syntax error. Just remove them.

CodePudding user response：

The line numbers are within each statement, and you have extra semicolons. So your:

CREATE TABLE department(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    department_name VARCHAR (30) NOT NULL;
);

is two statements. The first:

CREATE TABLE department(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    department_name VARCHAR (30) NOT NULL

complains about a syntax error at '' on line 3 (the end, where it is expecting a , or ) and sees neither).

And the second:

);

complains about a syntax error at ')' on line 1 (for obvious reasons).