MySQL error: Missing index for constraint

I am creating 2 tables in my database:

DROP TABLE IF EXISTS `med_pharmacy`; CREATE TABLE IF NOT EXISTS `med_pharmacy` ( `med_pharmacy_id` int(11) NOT NULL AUTO_INCREMENT, `med_id` int(11) NOT NULL, `med_barcode` varchar(45) DEFAULT NULL, `med_received` date DEFAULT NULL, `med_expiry` date DEFAULT NULL, `med_tablet` int(11) DEFAULT NULL, `med_pill` int(11) DEFAULT NULL, `clinic_id` varchar(45) DEFAULT NULL, PRIMARY KEY (`med_pharmacy_id`), KEY `fk_med_pharmacy_medication1_idx` (`med_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1261 DEFAULT CHARSET=utf8mb4; 

AND:

DROP TABLE IF EXISTS `medication`; CREATE TABLE `medication` ( `med_id` int(11) NOT NULL, `med_name` varchar(75) NOT NULL, `med_date_added` date DEFAULT NULL, `clinic_id` varchar(45) DEFAULT NULL, `med_type` varchar(15) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

And when I run the queries in wamp I got this error:

SQL query:

ALTER TABLE `med_pharmacy` ADD CONSTRAINT `fk_med_pharmacy_medication1` FOREIGN KEY (`med_id`) REFERENCES `medication` (`med_id`) ON DELETE CASCADE ON UPDATE CASCADE MySQL 

said: Documentation

#1822 - Failed to add the foreign key constaint. Missing index for constraint 'fk_med_pharmacy_medication1' in the referenced table 'medication'

The tables already exists but I changed one field.

2 Answers

The column referenced in a foreign key must be indexed. You need to add an index on medication.med_id. In fact, this should probably be the primary key of the table.

ALTER TABLE medication ADD PRIMARY KEY (med_id); 

if you are giving

ADD CONSTRAINT `fk_med_pharmacy_medication1` FOREIGN KEY (`med_id`) 

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

so med_id should have primary key in medication or reference the columns of a UNIQUE constraint