Database.Beginner.What is a primary key?

A primary key is a column (or a set of columns) in a database table that uniquely identifies each row in that table.

Key points about a primary key:

• Uniqueness: No two rows can have the same value in the primary key column(s).
• Non-null: A primary key column cannot have NULL values; every row must have a valid, unique key.
• Single vs Composite: It can be a single column (like id) or a combination of columns (called a composite key).
• Purpose: It ensures that each record can be uniquely identified, which is crucial for data integrity and for creating relationships between tables.

Example:

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

Here, user_id is the primary key.

If you define a table without specifying a primary key, you risk having duplicate or missing records that you can’t easily distinguish.

COMPOSITE PRIMARY KEY

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

Here:

• The combination of student_id and course_id together forms the primary key.
• This ensures that a student cannot enroll in the same course twice — but a student can enroll in multiple courses, and multiple students can enroll in the same course.

Key points:

• student_id alone is not unique.
• course_id alone is not unique.
• But together, (student_id, course_id) is unique.

---

If you want to add a composite primary key after creating the table:

ALTER TABLE Enrollment
ADD PRIMARY KEY (student_id, course_id);