Essential Keys in SQL: A Comprehensive Reference

Photo by Olivie Strauss on Unsplash

In SQL (Structured Query Language), keys play a crucial role in defining relationships between tables and ensuring the integrity of the data. There are several types of keys in SQL, each serving a specific purpose. Here are the key types commonly used:

1. Primary Key (PK):

• A primary key is a unique identifier for a record in a table.
• It must contain unique values and cannot have NULL values.
• Each table can have only one primary key.
• Example:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

2. Foreign Key (FK):

• A foreign key is a field that refers to the primary key in another table.
• It establishes a link between two tables, enforcing referential integrity.
• It can have duplicate values and can be NULL.
• Example:

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);