MySQL error: Missing index for constraint

Question

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.

score 22 · Accepted Answer · answered Mar 23 '18 at 06:33

22

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);

answered Mar 23 '18 at 06:33

Barmar

741,623
53
500
612

1

but I already had in my sql script the following: ALTER TABLE medication` ADD PRIMARY KEY (med_id);` – alim1990 Mar 23 '18 at 06:39
1

I suspect you have that *after* you try to add the foreign key. It has to be before it. – Barmar Mar 23 '18 at 06:40
Yeah I think that – alim1990 Mar 23 '18 at 06:41
Why don't you just put these things in your `CREATE TABLE` queries, instead of doing them separately with `ALTER TABLE`? – Barmar Mar 23 '18 at 06:41
because I exported the sql file from phpmyadmin. I am surprised as you. – alim1990 Mar 23 '18 at 06:45

SF..MJ · Answer 2 · 2018-03-23T06:44:14.647

6

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

edited Mar 23 '18 at 06:44

answered Mar 23 '18 at 06:35

SF..MJ

862
8
19

but I already had in my sql script the following: `ALTER TABLE `medication` ADD PRIMARY KEY (`med_id`);` – alim1990 Mar 23 '18 at 06:39
No, it does not. – Shadow Mar 23 '18 at 06:39

MySQL error: Missing index for constraint

2 Answers2

Linked

Related