Error Code: 1215. Cannot add foreign key constraint. Foreign key problem

Question

create table methodology(
method_id int primary key,
date_added date,
description varchar(1000) character set utf8);

create table text_item(
t_id int primary key,
date_written date,
original_language varchar(2),
description varchar(1000) character set utf8,
foreign key(date_written) references methodology(date_added));

I am trying to create a foreign key with the code : foreign key(date_written) references methodology(date_added)); but it keeps giving me the following error: Error Code: 1215. Cannot add foreign key constraint.

Answer 1

The exact error message is (emphasis is mine):

Failed to add the foreign key constraint. Missing index for constraint 'text_item_ibfk_1' in the referenced table 'methodology'.

This is clear enough. You need an index on the referenced column. Just create it:

create index idx_methodology_date_added on methodology(date_added);

Then you can create the text_item table.

Demo on DB Fiddle.

Note: while this will technically work, it is quite unclear what the purpose of this relation is; it you want to reference a row in methodology, you would better reference its primary key column (method_id).