C3

Referential Integrity

FK value MUST match the candidate key/primary key of its own or some other relation
Otherwise the FK MUST be NULL.

Foreign Keys (FK)

e.g.

Product

Prod_Code	SupplierId
AB1	S01
AB2	S02
AB3	S02
AB4	S02
AB5	S01

Supplier

SupplierId	SupplierName
S01	King Dress
S02	Ladies Green

Here the Product table refers to the Supplier table for the supplier name

In this case:

Product is the Referencing Table/Child relation

Supplier is the Referenced Table/Parent relation

e.g.

Creating Product Table:

CREATE TABLE Product (
  Prod_code char(3) not null,
  Supplier_Id char(3) null,

  PRIMARY KEY (Prod_code),
  FOREIGN KEY (Supplier_Id),
  REFERENCES Supplier(SupplierID)
)

We can also alter the table if it already has been created

ALTER TABLE Product
ADD FOREIGN KEY (SupplierId)
REFERENCES (SupplierId)

Referential Actions

Referential Action is an action that will occur IF referential integrity is about to be broken.

How can referential integrity be broken?

Lets say we want to remove supplier S02

Product

Prod_Code	SupplierId
AB1	S01
AB2	S02
AB3	S02
AB4	S02
AB5	S01

Supplier

SupplierId	SupplierName
S01	King Dress

Here we can see that S02 still exists in the Product Table even though it has been removed under the Supplier table.

Note that the referential actions will be the same for BOTH UPDATE AND DELETE.

Cascade

Cascade ensures that if a row in the parent relation is deleted, all rows will deleted from the child relations.

CREATE TABLE Product (
  Prod_code char(3) not null,
  Supplier_Id char(3) null,

  PRIMARY KEY (Prod_code),
  FOREIGN KEY (Supplier_Id),
  REFERENCES Supplier(SupplierID),
  ON DELETE CASCADE
)

ALTER TABLE Product
ADD FOREIGN KEY (SupplierId)
REFERENCES (SupplierId)
ON DELETE CASCADE

e.g. Product

Prod_Code	SupplierId
AB1	S01
AB5	S01

Supplier

SupplierId	SupplierName
S01	King Dress

Here we see that there are no longer any S02 SupplierIds inside of Product Table

NO ACTION

NO ACTION prevents the row from the parent relation from being delete IF rows in the child relation still references it.

CREATE TABLE Product (
  Prod_code char(3) not null,
  Supplier_Id char(3) null,

  PRIMARY KEY (Prod_code),
  FOREIGN KEY (Supplier_Id),
  REFERENCES Supplier(SupplierID),
  ON DELETE NO ACTION
)

ALTER TABLE Product
ADD FOREIGN KEY (SupplierId)
REFERENCES (SupplierId)
ON DELETE NO ACTION

SET NULL

SET NULL sets the child relation's FK to NULL if a row in the parent relation is deleted.

CREATE TABLE Product (
  Prod_code char(3) not null,
  Supplier_Id char(3) null,

  PRIMARY KEY (Prod_code),
  FOREIGN KEY (Supplier_Id),
  REFERENCES Supplier(SupplierID),
  ON DELETE SET NULL 
)

ALTER TABLE Product
ADD FOREIGN KEY (SupplierId)
REFERENCES (SupplierId)
ON DELETE SET NULL

e.g.

Product

Prod_Code	SupplierId
AB1	S01
AB2	NULL
AB3	NULL
AB4	NULL
AB5	S01

Supplier

SupplierId	SupplierName
S01	King Dress

Note how the FK is now set to NULL when S02 has been removed.

Joining Tables

Combines 2 or more tables based on a related column between them.

This allows us to retrieve data from multiple tables from a single query.

INNER JOIN (Default)

Joins 2 or more tables when they have matching values

e.g.

Product

Prod_Code	SupplierId
AB1	S01
AB2	S02
AB3	S02
AB4	S02
AB5	S01

Supplier

SupplierId	SupplierName
S01	King Dress
S02	Ladies Green

Let's say that we want to get the supplier name for each product code.

SELECT Prod_Code, Supplier_Name
FROM Product
INNER JOIN Supplier
ON Product.SUpplierID  = Supplier.SupplierId;

CROSS JOIN (Cartesian Product)

Joins and returns ALL possible combinations of rows from joined tables
This join doesn't have a condition (just joining without 'ON')

e.g.

Product

Prod_Code	SupplierId
AB1	S01
AB2	S02

Supplier

SupplierId	SupplierName
S01	King Dress
S02	Ladies Green

SELECT Prod_Code, Supplier_Name
FROM Product
JOIN Supplier

Results:

Prod_Code	Supplier_Name
AB1	King Dress
AB1	Ladies Green
AB2	King Dress
AB2	Ladies Green

Multi-Table Join

The code is quite similar to joining with 2 tables, but the ON clauses needs to include every single table.

e.g.

Product

Prod_Code	SupplierId
AB1	S01
AB2	S02

Supplier

SupplierId	SupplierName
S01	King Dress
S02	Ladies Green

Customer

CustomerId	Prod_Code
C1	AB1
C2	AB2

Lets say that we want to get the Customer Id, Product Code and SupplierName

e.g.

SELECT CustomerId, Prod_Code, SupplierName
FROM Customer
INNER JOIN Product
ON Customer.Prod_Code = Product.Prod_Code
INNER JOIN Supplier
ON Supplier.SupplierId = Product.SupplierId;

Expected Results:

CustomerId	Prod_Code	SupplierName
C1	AB1	King Dress
C2	AB2	Ladies Green

LEFT/RIGHT JOIN

Returns ALL rows from the left table and matching rows from the right table.
IF there is no match, the right table columns will just be NULL
Right join is just the opposite

Prod_Code	SupplierId
AB1	S01
AB2	S02
AB3	NULL

Supplier

SupplierId	SupplierName
S01	King Dress
S02	Ladies Green

SELECT Prod_Code, SupplierName
FROM Product
LEFT JOIN Supplier
ON Supplier.SupplierId = Product.SupplierId;

Expected Result: |Prod_Code|SupplierName| |-|-| |AB1|King Dress| |AB2|Ladies Green| |AB3| NULL |

Sub-query

Scalar Subquery

Returns a single value
Used in comparison operations

e.g.

Given this table Product

Prod_Code	Prod_Desc	Unit_px	Supplier_ID
HG7160	Sale Dress White	15.90	S1001
RQ0207	Dress White	18.60	S1002
HG7166	Dress Blue	15.90	S1003
HG6159	Sale Dress Pink	15.40	S1002

Let's say that we want to get the products where the Unit_px is above average

SELECT Prod_Code, Prod_Desc, Unit_Price
FROM Product
WHERE Unit_Price > 
(
  SELECT AVG(Unit_Price)
  FROM Product
);

Expected Results:

Prod_Code	Prod_Desc	Unit_px
RQ0207	Dress White	18.60

Table Subquery

One or more columns/rows (multiple values)
used as a list for comparison with 'IN' operators

e.g.

Given this table Sales:

Customer Id	Prod_Code
C1	HC7160
C2	RQ0207

Let's say that we want to get all product codes that have been sold

SELECT Prod_Code
FROM Product
WHERE Prod_Code 
IN (
  SELECT Prod_Code
  FROM Sales
);

Expected Results:

Prod_Code
HC7160
RQ0207