Click here to Skip to main content
15,887,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 27-Mar-22 23:33pm
v2

1 solution

Looking at the foreign key declaration, you seem to reference another database than the one where you create the tools table
SQL
CONSTRAINT fk_tools_location FOREIGN KEY  (tool_id)
REFERENCES flex.tools (tool_id)

Perhaps the database should be wax or the tools table definition in flex should be changed.
 
Share this answer
 
Comments
UT7 28-Mar-22 5:34am    
@Wendelius thanks for your response. Sorry I changed the name of the database from flex to wax before I posted this question, so I've edited the question. Thanks a lot.
Wendelius 28-Mar-22 12:29pm    
It seems that id column in tools is BIGINT(11) while tool_id in location id BIGINT. Try using matching data type i.e. either BIGINT(11) or BIGINT in both places
UT7 28-Mar-22 21:25pm    
@Wendelius, I changed the tool_id in the location table to BIGINT(11) but it didn't work, I still have the error. I wonder what "missing index constraint means" ? This seems to be the problem. Thanks for your help.
Wendelius 28-Mar-22 22:57pm    
The error means that the column that you're referring to, needs to be indexed, preferably a primary key or unique. The same message also occurs when the data types do not match in corresponding columns.

Looking at the fk definition again, is it intentional that you refer to tools.tool_id?
How that column differs from tools.id which is the primary key? I take it tools.id should be referred or otherwise you need to build an index on tools.tool_id, preferably unique.

UT7 28-Mar-22 23:32pm    
@Wendelius thanks a lot. Yeah, I sort of figured it out. I added an index to tool_id in the tools table --> INDEX idx_tool_id (tool_id). I dropped the tools table and recreated it with the modification above and it worked. Thanks a lot for your help, I am very grateful.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900