Failed to add foreign key constraint [duplicate]
0
This question already has an answer here:
Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint
4 answers
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);
mysql database foreign-keys
edited Nov 23 '18 at 8:07
FrankerZ
16.5k72862
asked Nov 23 '18 at 8:05
Teju_MTeju_M
102
marked as duplicate by Madhur Bhaiya, FrankerZ, Ravi, philipxy, Machavity Nov 23 '18 at 16:17
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
0
This question already has an answer here:
Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint
4 answers
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);
mysql database foreign-keys
edited Nov 23 '18 at 8:07
FrankerZ
16.5k72862
asked Nov 23 '18 at 8:05
Teju_MTeju_M
102
marked as duplicate by Madhur Bhaiya, FrankerZ, Ravi, philipxy, Machavity Nov 23 '18 at 16:17
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
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 Minimal, Complete, and Verifiable example. PS That includes cut & paste & runnable code.
– philipxy
Nov 23 '18 at 12:18
add a comment |
0
0
0
This question already has an answer here:
Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint
4 answers
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);
mysql database foreign-keys
edited Nov 23 '18 at 8:07
FrankerZ
16.5k72862
asked Nov 23 '18 at 8:05
Teju_MTeju_M
102
This question already has an answer here:
Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint
4 answers
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);
This question already has an answer here:
Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint
4 answers
mysql database foreign-keys
mysql database foreign-keys
edited Nov 23 '18 at 8:07
FrankerZ
16.5k72862
asked Nov 23 '18 at 8:05
Teju_MTeju_M
102
edited Nov 23 '18 at 8:07
FrankerZ
16.5k72862
asked Nov 23 '18 at 8:05
Teju_MTeju_M
102
edited Nov 23 '18 at 8:07
FrankerZ
16.5k72862
edited Nov 23 '18 at 8:07
FrankerZ
16.5k72862
edited Nov 23 '18 at 8:07
FrankerZ
16.5k72862
16.5k72862
asked Nov 23 '18 at 8:05
Teju_MTeju_M
102
asked Nov 23 '18 at 8:05
Teju_MTeju_M
102
asked Nov 23 '18 at 8:05
Teju_MTeju_M
102
102
marked as duplicate by Madhur Bhaiya, FrankerZ, Ravi, philipxy, Machavity Nov 23 '18 at 16:17
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Madhur Bhaiya, FrankerZ, Ravi, philipxy, Machavity Nov 23 '18 at 16:17
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
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 Minimal, Complete, and Verifiable example. PS That includes cut & paste & runnable code.
– philipxy
Nov 23 '18 at 12:18
add a comment |
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 Minimal, Complete, and Verifiable example. PS That includes cut & paste & runnable code.
– philipxy
Nov 23 '18 at 12:18
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 Minimal, Complete, and Verifiable example. PS That includes cut & paste & runnable code.
– philipxy
Nov 23 '18 at 12:18
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 Minimal, Complete, and Verifiable example. PS That includes cut & paste & runnable code.
– philipxy
Nov 23 '18 at 12:18
add a comment |
1 Answer
1
active
oldest
votes
0
Create separate index on ITEMS.Item_ID (you have currently composite index on Item_ID and event)
answered Nov 23 '18 at 8:14
AndreyAndrey
1144
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 9:05
Yes either just add another index (if you want that paired primary key)
– Andrey
Nov 23 '18 at 9:49
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Create separate index on ITEMS.Item_ID (you have currently composite index on Item_ID and event)
answered Nov 23 '18 at 8:14
AndreyAndrey
1144
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 9:05
Yes either just add another index (if you want that paired primary key)
– Andrey
Nov 23 '18 at 9:49
add a comment |
0
Create separate index on ITEMS.Item_ID (you have currently composite index on Item_ID and event)
answered Nov 23 '18 at 8:14
AndreyAndrey
1144
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 9:05
Yes either just add another index (if you want that paired primary key)
– Andrey
Nov 23 '18 at 9:49
add a comment |
0
0
0
Create separate index on ITEMS.Item_ID (you have currently composite index on Item_ID and event)
answered Nov 23 '18 at 8:14
AndreyAndrey
1144
Create separate index on ITEMS.Item_ID (you have currently composite index on Item_ID and event)
answered Nov 23 '18 at 8:14
AndreyAndrey
1144
answered Nov 23 '18 at 8:14
AndreyAndrey
1144
answered Nov 23 '18 at 8:14
AndreyAndrey
1144
answered Nov 23 '18 at 8:14
AndreyAndrey
1144
1144
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 9:05
Yes either just add another index (if you want that paired primary key)
– Andrey
Nov 23 '18 at 9:49
add a comment |
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 9:05
Yes either just add another index (if you want that paired primary key)
– Andrey
Nov 23 '18 at 9:49
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 9:05
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 9:05
Yes either just add another index (if you want that paired primary key)
– Andrey
Nov 23 '18 at 9:49
Yes either just add another index (if you want that paired primary key)
– Andrey
Nov 23 '18 at 9:49
add a comment |
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 Minimal, Complete, and Verifiable example. PS That includes cut & paste & runnable code.
– philipxy
Nov 23 '18 at 12:18