Failed to open the referenced table

Question

I am new with SQL and I am not entirely sure why I am getting the error: ERROR 1824 (HY000) at line 5: Failed to open the referenced table 'products' Operation failed with exitcode 1

Here is my code

drop database if exists cc;
create database cc /*!40100 default character set utf8 */;
use cc;

create table Customers(
  CustomerID int not null,
  FirstName varchar(255),
  LastName varchar(255),
  address varchar(255),
  phoneNO varchar(11),
  prodID int,
  quantity int,
  primary key (CustomerID),
  foreign key (prodID) references Products(itemID)
);

create table Employees(
   EmployeeID int not null,
   FirstName varchar(255),
   LastName varchar(255),
   address varchar(255),
   phoneNO varchar(11),
   ManagerID int not null,
   primary key (EmployeeID),
   foreign key (managerID) references Managers(mgrID)
 );

create table Managers(
    mgrID int not null,
    salary float,
   MaxSupervisingCapacity int,
   foreign key (mgrID) references Employees(EmployeeID),
   primary key (mgrID) 
);

Answer 1

I have Just fixed this problem by Cheking both tables Storage Engine same and by settings the same Attributes for reff tables column.

Answer 2

You can't create a foreign key that references another table until after you create that other table. In the Customers table you have

foreign key (prodID) references Products(itemID)

but there's no Products table yet. And in Employees you have

foreign key (managerID) references Managers(mgrID)

but the Managers table is created after it.

You need to reorder your table creations. Create Products before Customers.

Also, you can't have circular foreign key relationships. Employees.managerID references Managers and Managers.mgrID references Employees. This creates a chicken-and-egg problem: how would you create the first employee, since it needs a manager, but you can't create the first manager because it needs to refer to an employee.

You can solve this problem by allowing the foreign keys to be null. So you create the first employee with a null manager, then create the manager, then replace the managerID with this ID.

There's also a chicken-and-egg problem when creating these two tables. You can't reference a table that has yet to be created. So leave out the foreign key specification when creating the table, and add it later with ALTER TABLE.

create table Employees(
   EmployeeID int not null,
   FirstName varchar(255),
   LastName varchar(255),
   address varchar(255),
   phoneNO varchar(11),
   ManagerID int not null,
   primary key (EmployeeID)
 );

create table Managers(
    mgrID int not null,
    salary float,
   MaxSupervisingCapacity int,
   foreign key (mgrID) references Employees(EmployeeID),
   primary key (mgrID) 
);

alter table Employees add foreign key (managerID) references Managers(mgrID);

Answer 3

You can just surround the whole thing with a

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; 
SET FOREIGN_KEY_CHECKS=0;   

## all of your schema and inserts

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

This change of the check will cause the engine not to do checks on those keys and just add them as is.