Primary Keys & Foreign Keys

🔑 Primary Key

• A Primary Key is a column (or set of columns) that uniquely identifies each row in a table.
• Rules:
  • Must be unique.
  • Cannot be NULL.
  • A table can have only one primary key (but it can consist of multiple columns = composite key).

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT
);

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT
);

Here, StudentID is the Primary Key, which uniquely identifies each student.

🔗 Foreign Key

• A Foreign Key is a column (or set of columns) in one table that refers to the Primary Key in another table.
• It creates a relationship between two tables.
• Ensures referential integrity (cannot insert a value in the foreign key column that doesn’t exist in the referenced primary key).

Example:

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

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

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

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

Here:

• StudentID in Enrollments is a Foreign Key → References Students(StudentID).
• CourseID in Enrollments is a Foreign Key → References Courses(CourseID).

🔍 Example Data

Students Table

Courses Table

Enrollments Table

✅ Primary Key ensures uniqueness inside a table.
✅ Foreign Key links tables together.