Database.Beginner.What about composite primary key and many unique constraints ?

Composite Primary Key

A Primary Key doesn’t have to be just one column — it can be a combination of multiple columns.

That’s called a composite primary key.

✅ Example:

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)  -- Composite Primary Key
);

Here:

(student_id, course_id) together uniquely identify each row.
A student can enroll in multiple courses.
A course can have multiple students.
But the same student cannot enroll in the same course twice.

👉 Both columns together form the Primary Key.

And What About Many UNIQUE Constraints?

Even if you have a composite Primary Key, you can still add UNIQUE constraints on other columns (or other combinations of columns).

✅ Example:

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    email VARCHAR(100),
    PRIMARY KEY (student_id, course_id),   -- Composite PK
    UNIQUE (student_id, semester),         -- Unique Constraint #1
    UNIQUE (email)                         -- Unique Constraint #2
);

(student_id, course_id) → uniquely identifies a record (composite Primary Key).
(student_id, semester) → no student can be enrolled twice in the same semester (UNIQUE).
email → email must be unique (UNIQUE).

✅ Many UNIQUE constraints are still allowed, even with a composite Primary Key.

Important Rules

Primary Key	Unique Constraint
Only one Primary Key per table	You can have many UNIQUE constraints
Can be single-column or multi-column (composite key)	Each UNIQUE can be single or multi-column too
Must be `NOT NULL`	UNIQUE allows `NULL` values (in most DBs)
Primary Key creates a unique clustered index (in most DBs)	UNIQUE creates a unique non-clustered index

To sum up:

✅ Composite Primary Key — multiple columns combined to uniquely identify each row.
✅ Many UNIQUE constraints — additional uniqueness rules on other columns or other combinations of columns.

✅ You can absolutely have a composite primary key and many unique constraints in the same table.

Real-Life Analogy

Think of a university course enrollment system:

Primary Key = (StudentID + CourseID): No duplicate enrollment in the same course.
Unique constraint on (StudentID + Semester): No duplicate enrollment in the same semester.
Unique Email: No duplicate emails.

Each constraint enforces a different “business rule.”

Simple Example

CREATE TABLE Bookings (
    room_id INT,
    date DATE,
    start_time TIME,
    booking_id SERIAL UNIQUE,       -- Unique booking number
    user_email VARCHAR(255),
    PRIMARY KEY (room_id, date, start_time),   -- Composite PK (room + date + time)
    UNIQUE (user_email, date)                 -- User cannot have two bookings on the same day
);

Composite PK on (room_id, date, start_time).
Unique constraint on (user_email, date).
Unique constraint on booking_id.

✅ Perfectly valid!