Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 337
alim1990 Avatar asked Mar 23 '18 06:03

alim1990


People also ask

What is error 1005 in MySQL?

The table must have the correct column names and types. It must also have indexes on the referenced keys. If these requirements are not satisfied, MySQL returns Error 1005 that refers to errno: 150 in the error message, which means that a foreign key constraint was not correctly formed.

What is index constraint in MySQL?

Indexes and constraints are used to reinforce data integrity and increase database performance. Using indexes and constraints, you can access information from the database quicker and guarantee the referential integrity of information.

How do I fix error code 1822 in MySQL?

For foreign key relationship, the parent table column on which you are creating relation must be unique or primary and they must have the same datatype and size also. To resolve this make txtAC_tag column unique. ...and so therefore he should probably make txtAC_tag unique using a constraint +1.

How do I find missing indexes in SQL Server?

To determine which missing index groups a particular missing index is part of, you can query the sys. dm_db_missing_index_groups dynamic management view by equijoining it with sys. dm_db_missing_index_details based on the index_handle column. The result set for this DMV is limited to 600 rows.


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);
like image 101
Barmar Avatar answered Sep 22 '22 14:09

Barmar


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

like image 27
useless'MJ Avatar answered Sep 24 '22 14:09

useless'MJ