Error 1072 mysql

Я продолжаю получать ошибку 1072 при попытке добавить атрибут внешнего ключа и привязать мой атрибут к другой таблице. Я пробовал разные способы записи, но я продолжаю получать сообщение о том, что «department_id не существует в таблице». Если бы вы могли дать мне руку, которая была бы потрясающей! Огромное спасибо!

Вот мой код:

Alter table employee
add constraint department_fk
foreign key ( department_id)
references department(department_id);

И вот остальные мои таблицы:

Create table Department (
    department_id integer auto_increment primary key not null,
    department_name varchar (50) not null,
    Office_number varchar(50) not null,
    phone char (20) not null
);


Create table employee (
    employee_id integer auto_increment primary key not null,
    first_name varchar (25) not null,
    last_name varchar (25) not null,
    phone char(20) not null,
    email varchar (100) not null,
    salary decimal (10,2)
);

Create table project (
    project_id integer auto_increment primary key not null,
    max_hours time not null,
    start_date datetime not null,
    end_date datetime not null,
    Project_lead integer,
    Constraint project_fk1 
    foreign key (employee_id)
    references employee(employee_id),
    Department_id integer,
    Constraint project_fk2 
    foreign key (department_id) 
    references department (department_id)  
);

Create table assignment (
    employee_id integer not null,
    project_id integer not null,
    hours_worked time not null,
    primary key (employee_id, project_id),
    constraint assignment_fk1 
    foreign key(employee_id) 
    references employee(employee_id),
    constraint assignment_fk2 
    foreign key (project_id) 
    references project(project_id)
);

Alter table project
drop foreign key department_id;

Заранее спасибо!

I have researched thoroughly before asking this question including on this site.

I have a students table:

CREATE TABLE IF NOT EXISTS students(
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
middle_name VARCHAR(20),
last_name VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
password CHAR(40) NOT NULL,
reg_date DATETIME NOT NULL,
PRIMARY KEY (student_id),
UNIQUE (email));

I also have a subjects table:

CREATE TABLE IF NOT EXISTS subjects(
subject_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
subject_name VARCHAR(40) NOT NULL,
level_of_entry VARCHAR(20) NOT NULL,
exam_board VARCHAR(20) NOT NULL,
PRIMARY KEY (subject_id));

I am now creating a table to link the above tables:

CREATE TABLE IF NOT EXISTS entries(
exam_date DATETIME NOT NULL,
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (subject_id) REFERENCES subjects (subject_id)
);

My problem is that when I try to declare the foreign keys in the third table called entries, I get an error stating that the subject_id foreign key is not in the table referenced.
ERROR 1072 (42000) : Key column ‘student_id’ doesn’t exist in table, even though it is clearly contained inside the the students table and the same applies to ‘subject_id’ and the subject table.
I am certain that my syntax for declaring the foreign keys is correct so I am unsure how to fix the problem.
All help is appreciated. Thank you.

Answer 1

You forgot to create these two columns before applying your foreign key constraints :

CREATE TABLE IF NOT EXISTS entries(
exam_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_id INT UNSIGNED NOT NULL,
subject_id INT UNSIGNED NOT NULL,
exam_date DATETIME NOT NULL,
PRIMARY KEY (exam_id),
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (subject_id) REFERENCES subjects (subject_id)
);

EDIT :

I advise you to add in every table a unique ID column (here : exam_id).