In SQL, keys are essential for defining relationships between tables and ensuring data integrity. Here’s a detailed explanation of foreign keys, primary keys, unique keys, and composite keys, along with examples for each.

1. Primary Key

A primary key is a column (or a set of columns) that uniquely identifies each row in a table. A primary key must contain unique values, and it cannot contain NULL values. Each table can have only one primary key.

Example:

sql
1CREATE TABLE Employees (
2    EmployeeID INT PRIMARY KEY,
3    FirstName VARCHAR(50),
4    LastName VARCHAR(50),
5    Email VARCHAR(100)
6);

In this example, EmployeeID is the primary key for the Employees table. Each employee will have a unique EmployeeID, ensuring that no two employees can have the same ID.

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. Foreign keys are used to establish and enforce a link between the data in the two tables. A foreign key can accept NULL values unless specified otherwise.

Example:

sql
1CREATE TABLE Departments (
2    DepartmentID INT PRIMARY KEY,
3    DepartmentName VARCHAR(50)
4);
5
6CREATE TABLE Employees (
7    EmployeeID INT PRIMARY KEY,
8    FirstName VARCHAR(50),
9    LastName VARCHAR(50),
10    Email VARCHAR(100),
11    DepartmentID INT,
12    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
13);

In this example, DepartmentID in the Employees table is a foreign key that references the DepartmentID in the Departments table. This establishes a relationship between employees and their respective departments.

3. Unique Key

A unique key constraint ensures that all values in a column (or a set of columns) are unique across the table, similar to a primary key. However, unlike a primary key, a unique key can accept NULL values (but only one NULL per column).

Example:

sql
1CREATE TABLE Users (
2    UserID INT PRIMARY KEY,
3    Username VARCHAR(50) UNIQUE,
4    Email VARCHAR(100) UNIQUE
5);

In this example, both Username and Email are unique keys. This means that no two users can have the same username or email address, but they can have NULL values if not specified.

4. Composite Key

A composite key is a primary key that consists of two or more columns. The combination of these columns must be unique for each row in the table. Composite keys are used when a single column is not sufficient to uniquely identify a record.

Example:

sql
1CREATE TABLE CourseEnrollments (
2    StudentID INT,
3    CourseID INT,
4    EnrollmentDate DATE,
5    PRIMARY KEY (StudentID, CourseID)
6);

In this example, the combination of StudentID and CourseID forms a composite primary key for the CourseEnrollments table. This means that a student can enroll in multiple courses, and each enrollment must be unique based on the combination of StudentID and CourseID.

Summary

• Primary Key: Uniquely identifies each row in a table; cannot be NULL.
• Foreign Key: A column that creates a link between two tables; refers to the primary key in another table.
• Unique Key: Ensures all values in a column are unique; can accept NULL values.
• Composite Key: A primary key made up of two or more columns; used when a single column is not sufficient to uniquely identify a record.