Hello everyone, I want to create 2 tables in MySQL using Workbench. The first table is called tools which I was able to create but while I was creating the second table (location) I got this error
Error Code:1822 Failed to add the foreign key constraint. Missing index for constraint 'fk_tools_location' in the referenced table tools.
What I have tried:
This is the code that I used to create the two tables. The name of the database is wax
Tools Table
Use wax;
CREATE TABLE IF NOT EXISTS wax.tools (
id BIGINT(11) NOT NULL AUTO_INCREMENT,
tool_id BIGINT(11) NOT NULL,
tool_name VARCHAR(50) NOT NULL,
tag_id BIGINT(11) NOT NULL,
tag_type ENUM ('Active', 'Passive') NOT NULL,
note_1 LONGTEXT NULL,
note_2 LONGTEXT NULL,
note_3 LONGTEXT NULL,
PRIMARY KEY (id),
INDEX idx_tag_id (tag_id),
INDEX idx_tool_name (tool_name)
);
This for the Location Table
CREATE TABLE IF NOT EXISTS wax.location (
id BIGINT(11) NOT NULL AUTO_INCREMENT,
tool_id BIGINT NOT NULL,
location_id BIGINT(11) NOT NULL,
location_name VARCHAR (50) NOT NULL,
location ENUM ('HOME', 'ACTUAL') NOT NULL,
home_start_time TIME NOT NULL,
home_end_time TIME NULL,
actual_start_time TIME NULL,
actual_end_time TIME NULL,
time_spent_in_home TIME,
time_spent_in_actual TIME,
PRIMARY KEY (id),
INDEX idx_location_name (location_name),
INDEX idx_time_spent_in_home (time_spent_in_home ASC),
INDEX idx_time_spent_in_actual (time_spent_in_actual ASC),
CONSTRAINT fk_tools_location FOREIGN KEY (tool_id)
REFERENCES wax.tools (tool_id)
);
Please what I'm I doing wrong ? Thanks for any help.