Mysql ошибка 1824

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.

Источник

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.

Источник

Issue

I am building a database for a school project, but for some reason I cannon make a foreign key reference between 2 tables (only those 2). My project has 14 tables and it works fine for all the others.

The tables are made like:

create table degree(
    title varchar(50),
    idryma varchar(40),
    bathmida enum('High School', 'Univercity', 'Master', 'PHD'),
    constraint degree_id primary key (title, idryma)
);

create table has_degree(
    degree_title varchar(50),
    degree_idryma varchar(40),
    employee_username varchar(12),
    acquisition_year year(4),
    grade float(3,1),

    constraint has_degree_id primary key (degree_title, degree_idryma, employee_username)
);

And then I try to alter the table so that I make the foreign key connections:

alter table has_degree add foreign key (degree_title) references degree(title);
alter table has_degree add foreign key (degree_idryma) references degree(idryma);

But I keep on getting

Error Code: 1824. Failed to open the referenced table ‘degree’

I have tried to make them like that:

create table degree(
    title varchar(50),
    idryma varchar(40),
    bathmida enum('High School', 'Univercity', 'Master', 'PHD'),
    constraint degree_id primary key (title, idryma)
);

create table has_degree(
    degree_title varchar(50),
    degree_idryma varchar(40),
    employee_username varchar(12),
    acquisition_year year(4),
    grade float(3,1),

    foreign key (degree_title) references degree(title),
    foreign key (degree_idryma) references degree(idryma),
    
    /*employee is an other table that I use and that works just fine*/

    foreign key (employee_username) references employee(employee_username),
    constraint has_degree_id primary key (degree_title, degree_idryma, employee_username)
);

But the only thing that changes is that I get

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint ‘has_degree_ibfk_2’ in the referenced table ‘degree’

Solution

The columns in your foreign key in table has_degree must be the same as the columns in the primary key of the referenced table degree.

In this case, the primary key of degree consists of two varchar columns.

So the foreign key in has_degree that references it must also be only two varchar columns, and values in those columns in has_degree must match exactly the values in a row of degree.

You defined the foreign key this way:

foreign key (degree_title) references degree(title),
foreign key (degree_idryma) references degree(idryma),

But that’s two foreign keys, each having a single column. You need one foreign key with two columns:

foreign key (degree_title, degree_idryma) references degree(title, idryma),

Answered By – Bill Karwin

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Источник

I have a MySQL 5.7.x dump with about 12GB that I try to import into a newly installed MySQL 8.0.x local instance.

mysql -u username -p test < DB-210508.sql

The import starts but fails with the following message:

ERROR 1824 (HY000) at line 518: Failed to open the referenced table 'table-name'

I am wondering why there is a problem with foreign keys and if it is not more due to the fact that I am importing into 8.0.x

The MySQL 8.0.x instance is configured like this:

[mysqld]
default_authentication_plugin= mysql_native_password
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 32
innodb_buffer_pool_chunk_size = 134217728
collation_server        = utf8_unicode_ci
character_set_server    = utf8

What could be the reason for the failed import?

asked May 8, 2021 at 15:19

merlin's user avatar

2

This error happens when you have a classic “chicken and egg” problem. The most common situations for this would be:

  • populating a self-referencing table where a lower ID requires the existence of a higher ID
    ⇢ a good example of this would be an Employee table where record 33 had a manager with an ID of 999. 33 could not be recorded until 999 existed in the table.
  • populating a table that depends on another table that has not yet been created
    ⇢ this can happen when you have an Employee table that has references to User, but User isn’t created until after Employee.

The error you received was:

… Failed to open the referenced table 'table-name'

This points to the second item as being the most likely culprit.

The simplest way to resolve this would be to remove the foreign key checks when importing the data, then restore the checks after the import is complete. You can do this be editing your 12GB .sql file to have this at the very start:

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

Then, at the very end of the import file:

SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;

Note: With the foreign key check disabled, there is a possibility of recording bad data. If this is something you need to avoid for regulatory purposes, do not follow this “quick” merhod.

answered May 8, 2021 at 16:00

matigo's user avatar

matigomatigo

1,9781 gold badge5 silver badges15 bronze badges

Источник

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.

Источник

Понравилась статья? Поделить с друзьями:
  • Mysql ошибка 1410
  • Mtco performance ивеко стралис ошибка
  • Mtg arena код ошибки 1
  • Mysql ошибка 128
  • Mysql ошибка 1271