How is a Composite Key Different from a Primary Key?

💡 Concept Name

Primary Key – A unique identifier for a record in a database table. Composite Key – A primary key made up of two or more columns.

📘 Quick Intro

A primary key uniquely identifies each row in a table. When a single column is insufficient for uniqueness, multiple columns can be combined to form a composite key, which serves as the primary key.

🧠 Analogy / Short Story

Think of identifying a book in a library. The primary key is like the unique ISBN number that identifies a single book. But if you had to identify a book only by a combination of the author's name and book title (which might not be unique alone), that combination acts like a composite key.

🔧 Technical Explanation

Primary Key: A single column or minimal set of columns that uniquely identifies each record.
Composite Key: A key made up of two or more columns to ensure uniqueness.
A composite key is a type of primary key but uses multiple columns.
Both enforce uniqueness and cannot contain NULL values.

💻 Real Code Example

-- Primary key on a single column
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

-- Composite primary key on two columns
CREATE TABLE CourseRegistrations (
    StudentID INT,
    CourseID INT,
    RegistrationDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

❓ Interview Q&A

Q1: What is a primary key?
A: A column or set of columns that uniquely identifies each record in a table.

Q2: What is a composite key?
A: A primary key composed of two or more columns.

Q3: Can a table have more than one primary key?
A: No, a table can only have one primary key but it can be composite.

Q4: Can primary key columns contain NULL values?
A: No, primary key columns must be NOT NULL.

Q5: Why use composite keys?
A: When a single column cannot uniquely identify a record.

Q6: Are composite keys always the best choice?
A: Not always; sometimes surrogate keys are better.

Q7: How does a composite key enforce uniqueness?
A: By combining multiple columns to form a unique identifier.

Q8: Can composite keys be foreign keys?
A: Yes, composite keys can be referenced as foreign keys.

Q9: What is a surrogate key?
A: A synthetic key, usually a single column, used instead of a composite key.

Q10: What happens if primary key constraints are violated?
A: The database throws an error and rejects the operation.

📝 MCQs

Q1. What is a primary key?

Duplicates allowed
A unique identifier for each record
Can be NULL
Foreign key

Q2. What is a composite key?

A single column key
A primary key made of multiple columns
A foreign key
A unique index

Q3. Can a table have multiple primary keys?

Yes
No
Sometimes
Depends on database

Q4. Can primary keys contain NULL?

Yes
No
Sometimes
Only composite keys

Q5. Why use composite keys?

For faster queries
To ensure uniqueness when single column is insufficient
To allow NULLs
To store large data

Q6. Can composite keys be foreign keys?

No
Yes
Only in SQL Server
Only in Oracle

Q7. What is a surrogate key?

A composite key
A synthetic single-column key
A primary key
A foreign key

Q8. How does a composite key enforce uniqueness?

Using indexes
By combining multiple columns
Using triggers
By ignoring duplicates

Q9. What happens if primary key constraint is violated?

Database accepts it
Database rejects operation
Depends on DBMS
No effect

Q10. Can a composite key consist of just one column?

Yes
No
Sometimes
Depends on design

💡 Bonus Insight

Composite keys are useful in many-to-many relationship tables where uniqueness depends on multiple attributes. However, surrogate keys are often preferred in large systems for simplicity.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

📄 PDF Download Coming Soon