MySQL Error Code 1452. Altering table to add foreign key -

MySQL Error Code 1452. Altering table to add foreign key -

i removed foreign key constraints add together info in, later altered tables add together foreign keys in. however, 1 particular table thats beingness altered, maintain getting 1452 error code back.

data

insert invoice values (323, 'head injury patient', 'surgery , bed stay', 150.00, 918), (543, 'eye injury patient', 'medicine , hourly eye drops', 50.00, 910), (859, 'leg injury patient', 'surgery , bed stay', 100.00, 915);

adding foreign key

alter table invoice add together foreign key(medicine_id) references medicine(medicine_id);

construction of invoice

create table invoice ( invoice_id int, invoice_description varchar(150), treatment varchar (150) not null, hospital_cost int not null, medicine_id int, primary key(invoice_id) );

construction of medicine

create table medicine ( medicine_id int, medicine_name varchar(150), medicine_cost int not null, medicine_description varchar (150), brand_name varchar(150), manufacturer varchar(150), generic_name varchar(150), price_per_unit int, quantity_in_stock int, primary key(medicine_id) );

i believe error means there row in invoice table has medicine_id value not exist in medicine table.

to figure out values invoice not exist in medicine table, seek query:

select i.* invoice left bring together medicine m on i.medicine_id = m.medicine_id m.medicine_id null

you'll have either add together corresponding rows medicine table, or remove these rows invoice table.

mysql foreign-keys alter-table mysql-error-1452