Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

When I try to create the 'Project table I get this error Code: 1824. Failed to open the referenced table 'Employee'.

My syntax:

CREATE DATABASE IF NOT EXISTS Test;
USE Test;
CREATE TABLE IF NOT EXISTS Customer (
CustomerID VARCHAR(7) NOT NULL,
CustomerName VARCHAR(50),
CustAdress VARCHAR(70),
CustEmail VARCHAR(50),
PRIMARY KEY (CustomerID)
CREATE TABLE IF NOT EXISTS Employee (
EmpID VARCHAR(7) NOT NULL,
EmpName VARCHAR(50),
Position VARCHAR(30),
EmpTimePrice INT(4),
PRIMARY KEY (EmpID)
CREATE TABLE IF NOT EXISTS Project (
ProjectNo VARCHAR(7),
ProjectName VARCHAR(50),
StartDate DATE,
ProjTimePrice INT(6),
CustomerID VARCHAR(7),
EmpID VARCHAR(7),
PRIMARY KEY (ProjectNo),
FOREIGN KEY (EmpID) REFERENCES Employee (EmpID),
FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) 
CREATE TABLE IF NOT EXISTS ProjectWork (
ProjectNo VARCHAR(7),
EmpID VARCHAR(7),
PWDATE DATE,
HoursWorked INT(5),
FOREIGN KEY (ProjectNo) REFERENCES Project (ProjectNo),
FOREIGN KEY (EmpID) REFERENCES Employee (EmpID)
The names look correct to me and I have referenced the foreign key so I don't understand why I get this error. Any help would be appreciated, thanks.
                look this tutorial mysqltutorial.org/mysql-foreign-key , maybe you missed the ON UPDATE / ON DELETE actions
– shushu304
                Dec 6, 2018 at 13:21
                @shushu304 if you read carefully the official documentation, you can see the ON UPDATE/CASCADE are between brackets -> optional
– Cid
                Dec 6, 2018 at 13:23
                If your tables were previously created, their structure may be differents. If this is for testing or developpement, and not in production and the potentials datas can be lost, add before the lines CREATE TABLE IF NOT EXISTS this instruction : DROP TABLE IF EXISTS tableName; which will delete the table and its content
– Cid
                Dec 6, 2018 at 13:26
                VARCHAR shouldn't be a problem for an index, but it's preferred to use an INT, the size in octet being lower
– Cid
                Dec 6, 2018 at 13:40
                wow I was on a flight to hawaii and spent 8 hours trying to figure this out without the internet.
– Llama
                Mar 14, 2021 at 12:03
                You saved my life. Had to go through more than 50 stack overflow questions to find this. Thank you.
– DKage
                Apr 19, 2022 at 0:51
                I am way late to this thread, but I would say the case shouldn't matter as SQL is not case sensitive.
– Josepch
                Apr 3, 2022 at 23:31
One of the easiest ways to get this error is referencing a table that doesn't exist yet. Eg if you run the following code on a new schema:
create table ttest (bob varchar(10) not null, 
    constraint fkbob foreign key (bob) references other(bob) );
... you will get the MySQL error:
Error Code: 1824. Failed to open the referenced table 'other'
Because the other table doesn't exist.
The code in the question above creates Employee before referencing it. However, note that the CREATE DATABASE and CREATE TABLE statements have IF EXISTS, but there are no DROP IF EXISTS statements, as when creating a new database. If you were debugging a table creation script, changing and fixing things as you go, it would have been pretty easy to get into an inconsistent state where the tables were out of synch with the code. This would also explain why other people could run the code cleanly, because these tables didn't exist in their schema yet.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.