Why do we need "Relationships" between tables at all?

Question

Say we have the following 2 tables:

CREATE TABLE tblProduct
(`Product_ID` int, `Product_Name` varchar(7));

and

CREATE TABLE tblProductExtended
(`Product_ID` int, `Product_Size` int, `Product_Quantity` int);

With these values:

INSERT INTO tblProduct
    (`Product_ID`, `Product_Name`)
VALUES
    (1, 'Shoes1'),
    (2, 'Shoes2');

And

INSERT INTO tblProductExtended
    (`Product_ID`, `Product_size`, `Product_Quantity`)
VALUES
    (1, 36, 20),
    (1, 37, 20),
    (1, 38, 30),
    (2, 36, 50),
    (2, 37, 60),
    (2, 37, 75);

Now obviously, the Product_ID in the tblProductExpanded should be the FK of Product_ID of tblProduct. However, I can still query:

SELECT tblProduct.Product_ID, Product_Name, Product_Size, Product_Quantity
FROM tblProduct
INNER JOIN tblProductExtended ON tblProduct.Product_id = tblProductExtended.Product_id

Which returns:

Product_ID  Product_Name    Product_Size    Product_Quantity
1   Shoes1  36  20
1   Shoes1  37  20
1   Shoes1  38  30
2   Shoes2  36  50
2   Shoes2  37  60
2   Shoes2  37  75

So can someone please explain to me, why do we need "Relationships" at all if we can Query the same info regardless of relationship (AFAIK)? What is the actual use of them, apart from having to spend time setting them up.

Answer 1

I think the main reason is for data integrity. If you have a relationship between two tables invoice and invoice_item. You won't be able to add an invoice item that does not related to an invoice. This is what you want!

It's called referential integrity. It can get pretty messy in your database if you don't have foreign keys because you could delete and corrupt your data easily.

e.g. You could delete invoice and not it's corresponding invoice items. This is considered bad.

I think this gives you something to go on.