Getting error in mySQL for references and foreign key

Question

hello i keep getting this error when i try and execute my piece of sql code

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 '(department.id) )' at line 7 0.015 sec

here is my code:

DROP DATABASE IF EXISTS employee_db;

CREATE DATABASE employee_db;

USE employee_db;

CREATE TABLE department(
id INTEGER NOT NULL auto_increment,
name VARCHAR(30),
PRIMARY KEY(id)
);

CREATE TABLE roles(
id INTEGER NOT NULL auto_increment,
title VARCHAR(30),
salary DECIMAL,
department_id INTEGER,
PRIMARY KEY(id),
FOREIGN KEY(department_id) REFERENCES(department.id)
);

CREATE TABLE employee(
id INTEGER NOT NULL auto_increment,
first_name VARCHAR(30),
last_name VARCHAR(30),
role_id INTEGER,
manager_id INTEGER,
FOREIGN KEY(manager_id) REFERENCES(employee.id),
PRIMARY KEY(id),
FOREIGN KEY(role_id) REFERENCES(roles.id)
)

if anyone has any suggestions that would be awesome.

i'm using mySQL workbench on 8.0.22 on windows. thank you if are reading this!

Answer 1

This is not valid SQL:

FOREIGN KEY(department_id) REFERENCES(department.id)

Instead, use:

FOREIGN KEY(department_id) REFERENCES department(id)

That is: the foreign key references <table name>(<column name>). The dot notation is something else, that is used within queries to refer to the column of a table (or derived table).

The same error is repeated in all the foreign keys declarations of your script.

Here is a db fiddle with all required changes.