Primary Key and Foreign Key in SQL | Part 2

Primary Key and Foreign Key in SQL

A Primary Key uniquely identifies records within its own table and A Foreign Key links two tables together, enforcing a relationship between them by referencing the primary key of another table.

Objective:

To demonstrate the relationship between a Parent Table (Customers) and a Child Table (Orders), we will use Primary Key in the Customers table and Foreign Key in the Orders table to establish a relationship between them.

• Customers Table: This will be the Parent Table with a Primary Key (CustomerID).
• Orders Table: This will be the Child Table with a Foreign Key (CustomerID) that references the Primary Key from the Customers table.

SQL Code to Demonstrate Primary and Foreign Key Relationship

Step 1: Creating the Customers (Parent) Table with a Primary Key

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,  -- Primary Key for Customers table
    CustomerName VARCHAR(100),
    Email VARCHAR(100),
    Phone VARCHAR(15)
);

Explanation:

• CustomerID: A Primary Key that uniquely identifies each customer in the Customers table. It cannot have NULL values and must be unique.
• Other columns like CustomerName, Email, and Phone store additional details about the customers.

Step 2: Creating the Orders (Child) Table with a Foreign Key

1. Foreign Key in Table Creation

When you create a table, you can define the foreign key within the CREATE TABLE statement.

CREATE TABLE ChildTable (
    Column1 DataType PRIMARY KEY,   -- Primary Key in ChildTable
    Column2 DataType,
    ForeignKeyColumn DataType,      -- Foreign Key Column
    CONSTRAINT fk_name FOREIGN KEY (ForeignKeyColumn)
    REFERENCES ParentTable(PrimaryKeyColumn)  -- Reference to ParentTable
);

• ForeignKeyColumnis the column in the child table that will hold the foreign key.
• ParentTable(PrimaryKeyColumn) specifies the parent table and its primary key that the foreign key references.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,     -- Primary Key for Orders table
    OrderDate DATE,
    CustomerID INT,              -- Foreign Key referencing CustomerID in Customers table
    Amount DECIMAL(10, 2),
    CONSTRAINT fk_customer FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)  -- Establish Foreign Key relationship with Customers
);

Explanation:

• OrderID: A Primary Key for the Orders table, ensuring each order is unique.
• CustomerID: A Foreign Key in the Orders table that references the Primary Key (CustomerID) in the Customers table. This creates a link between the two tables, ensuring that each order belongs to an existing customer.
• The CONSTRAINT fk_customer ensures referential integrity by enforcing that any value in CustomerID in the Orders table must already exist in the Customers table.

Step 3: Inserting Data into Customers Table

INSERT INTO Customers (CustomerID, CustomerName, Email, Phone)
VALUES 
(1, 'John Doe', 'john@example.com', '555-1234'),
(2, 'Jane Smith', 'jane@example.com', '555-5678');

Explanation: Two customer records are added to the Customers table with CustomerID values of 1 and 2.

Step 4: Inserting Data into Orders Table

INSERT INTO Orders (OrderID, OrderDate, CustomerID, Amount)
VALUES 
(101, '2024-09-25', 1, 250.00),  -- Valid CustomerID from Customers table
(102, '2024-09-26', 2, 150.00);  -- Valid CustomerID from Customers table

Explanation: Two orders are inserted into the Orders table. The CustomerID in each order corresponds to the CustomerID from the Customers table, linking each order to an existing customer.

Step 5: Verifying the Relationship

To see the relationship between the two tables, we can use a JOIN query.

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName, Orders.Amount
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Output:

Explanation: This query joins the Orders and Customers tables based on the Foreign Key relationship. It shows that each order is linked to a valid customer.

Customers Table (Parent Table):

• Contains the Primary Key (CustomerID), uniquely identifying each customer. Orders Table (Child Table):
• Contains a Foreign Key (CustomerID) that references the Primary Key from the Customers table, enforcing a relationship between the two tables.

This setup ensures that orders can only be placed by existing customers, maintaining referential integrity.

Foreign Key in Table Creation

When you create a table, you can define the foreign key within the CREATE TABLE statement.

CREATE TABLE ChildTable (
    Column1 DataType PRIMARY KEY,   -- Primary Key in ChildTable
    Column2 DataType,
    ForeignKeyColumn DataType,      -- Foreign Key Column
    CONSTRAINT fk_name FOREIGN KEY (ForeignKeyColumn)
    REFERENCES ParentTable(PrimaryKeyColumn)  -- Reference to ParentTable
);