Nsryjdtyk

so I was creating a database and this was the error I kept getting on my last table

ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'invoice_ibfk_3' in the referenced table 'ITEMS'

I'm still not sure where exactly I'm going wrong

This is the rest of my SQL code:

    mysql> create table CUSTOMER(

    -> Customer_ID int AUTO_INCREMENT, 

    -> Customer_FName varchar(20),

    -> Customer_LName varchar(20), 

    -> Address varchar(20), 

    -> Phone_No varchar(20), 

    -> primary key(Customer_ID));

Query OK, 0 rows affected (0.06 sec)



mysql> create table FLORIST(

    -> Florist_ID varchar(10), 

    -> FName varchar(20),

    -> LName varchar(20),

    -> Contact_No varchar(20),

    -> Username varchar(20),

    -> Password varchar(100),  

    -> primary key(Florist_ID));

Query OK, 0 rows affected (0.02 sec)



mysql> create table ITEMS(

    -> Item_ID int AUTO_INCREMENT, 

    -> Price int,

    -> Event varchar(20),

    -> Name varchar(20), 

    -> Stock int,

    -> primary key(Item_ID, Event));

Query OK, 0 rows affected (0.05 sec)



mysql> create table ORDERS(

    -> Order_ID int AUTO_INCREMENT, 

    -> Customer_ID int, 

    -> Florist_ID varchar(10), 

    -> Order_Date date, 

    -> Due_Date date, 

    -> primary key(Order_ID), 

    -> foreign key(Customer_ID) references CUSTOMER(Customer_ID) on delete cascade on update cascade, 

    -> foreign key(Florist_ID) references FLORIST(Florist_ID) on delete cascade on update cascade);

And this is the last table that's problematic:

mysql> create table INVOICE(

    -> Order_ID int, 

    -> Item_ID int, 

    -> Quantity int, 

    -> Price_Per_Item int, 

    -> Event varchar(20), 

    -> foreign key(Order_ID) references ORDERS(Order_ID) on delete cascade on update cascade, 

    -> foreign key(Item_ID) references ITEMS(Item_ID) on delete cascade on update cascade, 

    -> foreign key(Event) references ITEMS(Event) on delete cascade on update cascade);

Create separate index on ITEMS.Item_ID (you have currently composite index on Item_ID and event)