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.
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With