Understanding Primary Keys and Foreign Keys in SQL

Introduction:
In the world of relational databases, primary keys and foreign keys are fundamental concepts that ensure data integrity and facilitate efficient data retrieval. They establish relationships between tables and play a crucial role in maintaining data consistency. In this article, we will delve into the concepts of primary keys and foreign keys, explore their significance, and provide SQL examples to illustrate their usage.

1. Primary Key:
   A primary key is a column or set of columns in a database table that uniquely identifies each row. It ensures that each record has a unique identifier, eliminating duplicate or null values. Key features of primary keys include:

• Uniqueness: Each value in the primary key column(s) must be unique within the table.
• Non-nullability: Primary key values cannot be null.
• Immutability: Once assigned, primary key values should not change.
• Single-value constraint: A primary key column can have only one value per row.

Example:

CREATE TABLE Customers (
   CustomerID INT PRIMARY KEY,
   Name VARCHAR(50),
   Email VARCHAR(100),
   Phone VARCHAR(20)
);

2. Foreign Key:
   A foreign key establishes a link or relationship between two tables based on the values of a column(s) in one table that corresponds to the primary key in another table. It maintains referential integrity, ensuring that data remains consistent across tables. Key features of foreign keys include:

• References a primary key: A foreign key column refers to the primary key column in another table.
• Enforces referential integrity: The foreign key value must exist as a primary key value in the referenced table or be null.
• Supports relationships: Foreign keys enable one-to-one, one-to-many, or many-to-many relationships between tables.

Example:

CREATE TABLE Customers (
   CustomerID INT PRIMARY KEY,
   Name VARCHAR(50),
   Email VARCHAR(100),
   Phone VARCHAR(20)
);

CREATE TABLE Orders (
   OrderID INT PRIMARY KEY,
   CustomerID INT,
   OrderDate DATE,
   FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

3. Relationship Types:
   a. One-to-One Relationship:
   In a one-to-one relationship, each record in one table is associated with exactly one record in another table. The foreign key column appears in either table.

Example:

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY,
   Name VARCHAR(50),
   -- Other columns
);

CREATE TABLE EmployeeDetails (
   EmployeeID INT PRIMARY KEY,
   Address VARCHAR(100),
   -- Other columns
   FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

b. One-to-Many Relationship:
In a one-to-many relationship, each record in one table can be associated with multiple records in another table. The foreign key appears in the “many” side table.

Example:

CREATE TABLE Departments (
   DepartmentID INT PRIMARY KEY,
   Name VARCHAR(50),
   -- Other columns
);

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY,
   Name VARCHAR(50),
   DepartmentID INT,
   -- Other columns
   FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

c. Many-to-Many Relationship:
In a many-to-many relationship, multiple records in one table can be associated with multiple records in another table. It requires an intermediary table, often referred to as a junction or bridge table, to establish the relationship.

Example:

CREATE TABLE Students (
   StudentID INT PRIMARY KEY,
   Name VARCHAR(50),
   -- Other columns
);

CREATE TABLE Courses (
   CourseID INT PRIMARY KEY,
   Title VARCHAR(100),
   -- Other columns
);

CREATE TABLE

 Enrollments (
   StudentID INT,
   CourseID INT,
   PRIMARY KEY (StudentID, CourseID),
   FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
   FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Conclusion:
Primary keys and foreign keys are fundamental concepts in SQL that establish relationships between tables and maintain data integrity. Primary keys uniquely identify each record in a table, while foreign keys establish relationships between tables based on primary key values. Understanding and implementing these concepts correctly are essential for effective database design and ensuring accurate data management. By utilizing primary keys and foreign keys appropriately, you can create robust and reliable database systems that support complex data relationships and queries.