Error Code: 1824. Failed to open the referenced table even though it is already created

user_13365510 0
Viewed 645

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'

datatables mysql sql
1 Answers

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),
Bill_Karwin_208600
Related