Primary Key and Foreign Key in SQL | Part 1

Primary Key and Foreign Key in SQL

In SQL, Primary Key and Foreign Key are essential concepts used to define relationships between tables and ensure data integrity.

1. Primary Key:

A Primary Key is a column or a set of columns that uniquely identifies each row in a table. The primary key enforces uniqueness and ensures that no two rows can have the same value in the primary key column(s). It also ensures that the column values cannot be NULL.

Properties:

• Uniquely identifies each record in a table.
• Cannot have NULL values.
• Each table can have only one primary key.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,  -- Primary Key
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

In this example, EmployeeID is the primary key, ensuring that each employee has a unique EmployeeID.

---

2. Foreign Key:

A Foreign Key is a column or a set of columns in one table that refers to the Primary Key in another table. A foreign key establishes a relationship between two tables and enforces referential integrity, meaning that the foreign key column(s) in the child table must contain values that exist in the primary key column(s) of the parent table.

Properties:

• Creates a relationship between two tables.
• Ensures that the value in the foreign key column must exist in the primary key column of the related table.
• Can accept NULL values unless specified otherwise.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,  -- Primary Key in Orders table
    OrderDate DATE,
    CustomerID INT,           -- Foreign Key referencing Customers table
    CONSTRAINT fk_customer FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)
);

In this example, CustomerID is a foreign key that links the Orders table to the Customers table. It ensures that every CustomerID in the Orders table must already exist as a CustomerID in the Customers table.

---

Key Differences:

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

---