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.

Primary Key and Foreign Key in SQL | Part 1

Pranav Bakare ・ Sep 29

#sql #backend #fullstack #devops

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.

Objective:

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
);

ForeignKeyColumn is 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
);

Primary Key and Foreign Key in SQL | Part 2