Failed to add foreign key constraint

Question

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

Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. PS That includes cut & paste & runnable code. — philipxy, Nov 23 '18 at 12:18

score 0 · Accepted Answer · answered Nov 23 '18 at 08:14

0

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

answered Nov 23 '18 at 08:14

Andrey

1,752
18
17

Oh, I see, so I should declare it as primary key(Items_ID) separately and not paired with Event? – Teju_M Nov 23 '18 at 09:05
Yes either just add another index (if you want that paired primary key) – Andrey Nov 23 '18 at 09:49

Failed to add foreign key constraint

1 Answers1