0
drop database if exists foodtruck_DB;
create database if not exists foodtruck_DB;
use foodtruck_DB;

create table if not exists Customer (
phone char(10),
email varchar(50),
pnum_FK Varchar(10),
constraint customer_PK primary key (email,pnum_fk) );

INSERT INTO Customer(pnum_FK, email, phone) VALUES
("PID101", "[email protected]","2155552256"),
("PID102","[email protected]","2155515975"),
("PID103","[email protected]","2675589787"),
("PID104","[email protected]","2674847485"),
("PID105","[email protected]","2154567895");

create table if not exists customer_order (
order_num varchar(10),
otype  char(10),
email_FK varchar(50),
pnum char(10),
constraint customer_order_PK primary key (order_num),
constraint customer_order_FK foreign key (email_FK)
references Customer(email) );

INSERT INTO Customer_order(order_num,otype,pnum,email_FK) VALUES
("Order#919","Delivery","PID209","[email protected]"),
("Order#920","Delivery","PID210","[email protected]"),
("Order#921","Delivery","PID211","[email protected]"),
("Order#922","Delivery","PID212","[email protected]");


create table if not exists Order_item(
autogen int auto_increment,
order_num_FK varchar(10),
item_num char(10),
constraint order_item_PK primary key(autogen),
constraint order_item_FK foreign key (order_num_FK)
references customer_order(order_num) );

INSERT INTO Order_item(item_num,order_num_FK) VALUES 
("Invo#10","2"),
("Invo#11","3"),
("Invo#12","5"),
("Invo#13","2"),
("Invo#14","1");

create table if not exists point_of_sale (
amount double,
paytype varchar(10),
CusID varchar(25),
itemID char(25),
reciptID varchar(25),
constraint point_of_sale_PK primary key (reciptID) );

INSERT INTO point_of_sale(CusID,itemID,reciptID,paytype,amount) VALUES
('s208c','RID#','222','Credit','10.47'),
('mwtqg','RID#','223','Cash','15.55'),
('c1g4n','RID#','224','Credit','7.5'),
('3wns6','RID#','225','Credit','20.15'),
('bph7o','RID#','226','Cash','30.1');

create table if not exists product(
pro_num varchar(5),
pro_name varchar(50),
category varchar(50),
reorder_lvl varchar(10),
itemID_FK varchar(10),
price double,
constraint product_pK primary key (pro_num),
constraint product_FK foreign key (itemID_FK) references point_of_sale(itemID));

insert into product(pro_num,pro_name,category,reorder_lvl,itemID_FK,price) values
('PR0#','299','pickles','Food','high','I44','1'),
('PR0#','300','hot sauce','Sauce','low','I45','1.5'),
('PR0#','301','olives','food','low','I46',  '0.5'),
('PR0#','302','mustard','Sauce','high','I47','0.5'),
('PR0#','303','tomatoes','food','high', 'I48','1');

create table if not exists vendor (
ven_name varchar(25),
venID varchar(10),
constraint vendor_PK primary key (venID) );

1 Answer 1

1

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table.

It will reject any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.

  1. In the customer_order table you are trying to insert ("Order#922","Delivery","PID212","[email protected]"); with the email constraint and the value [email protected] doesn't exist on Customer table.

  2. In the following insert there isn't any order_num_FK value which exists on order_num column on customer_order table

    INSERT INTO Order_item(item_num,order_num_FK) VALUES ("Invo#10","2"), ("Invo#11","3"), ("Invo#12","5"), ("Invo#13","2"), ("Invo#14","1");

  3. itemID in the point_of_sale table should be indexed and with same datatype and length with itemID_FK column on product table for the following constraint to work

    constraint product_FK foreign key (itemID_FK) references point_of_sale(itemID));

  4. You haven't included any of the values of itemID_FK aren't included on the itemID column of the point_of_sale table which makes the following constraint fails.

constraint product_FK foreign key (itemID_FK) references point_of_sale(itemID)

Note, I made some changes/fixes on this working fiddle

Must read

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.