Why are my sql foreign key values == null?-CodePudding

Im new to sql, I wrote these create table statements and inserted data into them. in the coffee table shop_id and supplier_id are foreign keys for tables I have already inserted data into. When I do my select statement these two values are null. why is this?

shop_id and supplier_id are null

# CREATE TABLES
CREATE TABLE COFFEE_SHOP (
    shop_id int NOT NULL ,
    shop_name varchar(50),
    city varchar(50),
    state CHAR (2),
    PRIMARY KEY (shop_id)
);

CREATE TABLE SUPPLIER (
    supplier_id int NOT NULL ,
    company_name varchar(50),
    country varchar(30),
    sales_contact_name varchar (50),
    email varchar(50) NOT NULL,
    PRIMARY KEY (supplier_id)
);

CREATE TABLE COFFEE (
    coffee_id int NOT NULL ,
    shop_id int,
    supplier_id int,
    coffee_name VARCHAR (30),
    price_per_pound numeric(5,2),
    PRIMARY KEY (coffee_id),
    FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id),
  FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id)
);

CREATE TABLE EMPLOYEE (
    employee_id int NOT NULL ,
    FirstName varchar(30),
    LastName varchar(30),
    hire_date date,
    job_title varchar(30),
    shop_id int,
    PRIMARY KEY(employee_id),
    FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id)
);

# INSERTION QUERIES 

INSERT INTO COFFEE_SHOP (shop_id, shop_name, city,state)
VALUES ('45', 'Stavanger', 'Norway','fl');

INSERT INTO SUPPLIER (supplier_id, company_name, country,sales_contact_name,email)
VALUES ('25', 'lovanger', 'Forway','Tl','[email protected]');

INSERT INTO COFFEE (coffee_id,coffee_name,price_per_pound )
VALUES ('15','espresso','15');

SELECT *
FROM COFFEE

CodePudding user response：

Hey you are missing the concept of Foreign Key.

It is never for Foreign key to have the values directly from another table when you have defined Foreign key in first table. what it simply means that you are tying two tables in order to make sure that no data is inserted in another table which doesn't have a reference key in first table.

To make it easier for you to understand, lets take your coffee table example. Let's suppose you are trying to insert a row in Coffee table with a supplier Id which is not present in Supplier Table.

INSERT INTO COFFEE (coffee_id,coffee_name,price_per_pound,supplier_id )
VALUES ('15','espresso','15','111');

This will error out as

Schema Error: Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key 
constraint fails (`test`.`COFFEE`, CONSTRAINT `COFFEE_ibfk_2` FOREIGN KEY 
(`supplier_id`) REFERENCES `SUPPLIER` (`supplier_id`))

Which means you can't insert any row in coffee table with Supplier Id which is not already present in Supplier table. That's the whole fundamental of Foreign Key and it's use. So that no corrupt data is inserted in tables.

This foreign key establishes referential integrity between Supplier and Coffee tables, thus, restricting the insertion of a new row when the SupplierId value of the inserted row does not match the ID column values of the Supplier's table.

Let me know if you have any further doubt

CodePudding user response：

Simply because you don't insert shop_id and supplier_id values to the table, you should also insert these values as well. Here:

INSERT INTO COFFEE (coffee_id,coffee_name,price_per_pound,shop_id,supplier_id)
VALUES ('15','espresso','15','45','25');