Composite and Alternate Keys in DBMS: Worked Example and Exam Traps
Learn how composite, candidate, primary and alternate keys differ. Derive two candidate keys from one relation, enforce them in SQL, and avoid common classification traps.
KnowledgeGate Team
Exam prep & CS education

One key can carry more than one label, and that is where many DBMS answers go wrong. A key may be composite because it uses several attributes, alternate because it was not selected as primary, or both. The relation moves from declared business rules through candidate-key proof, SQL constraints and exam-style decisions. The four sample rows test specific claims, but accidental uniqueness in a small table never replaces the stated rules.
Composite, candidate, primary and alternate answer different questions
A superkey is any set of attributes that uniquely identifies a tuple. A candidate key is a minimal superkey, so none of its attributes can be removed while preserving that ability. The primary key is the candidate key selected as the table's designated identifier. Every candidate key not selected is an alternate key.
A composite key contains two or more attributes. A simple key contains one. These labels answer a different question from primary and alternate: composite describes attribute count, while primary and alternate describe selection among candidate keys. Therefore, a composite candidate key may be primary or alternate, and an alternate key may be simple or composite.
Question | Label | Test |
|---|---|---|
Does it uniquely identify? | Superkey | Its closure contains every relation attribute |
Is it minimal? | Candidate key | No proper subset is a superkey |
Was it selected? | Primary or alternate | Selected means primary; unselected means alternate |
How many attributes? | Simple or composite | One means simple; two or more means composite |
These keys arise after we map entities and relationships into relations. Their labels come from the resulting dependencies.
A unique combination is not automatically a composite candidate key
Test a proposed attribute set in two stages. First, compute its closure to see whether it determines every attribute. Second, remove one attribute at a time and repeat the test. If any proper subset still determines the whole relation, the original set is a superkey but not a candidate key.
For R(A, B, C, D), the result {A, B}+ = {A, B, C, D} proves that {A, B} is a superkey. It becomes a candidate key only if {A}+ and {B}+ both fall short. A multi-column UNIQUE constraint should not be called a candidate key until minimality and non-nullability have also been established.
Rows provide a useful negative test. Repeated values can disprove a proposed key, but four distinct values cannot prove a permanent business rule. Declared rules and functional dependencies come before the row checks.
Worked example: derive both candidate keys step by step

Consider REGISTRATION(RegistrationNo, StudentID, CourseCode, Term, Status). The business rules are:
Each
RegistrationNobelongs to exactly one registration:RegistrationNo -> StudentID, CourseCode, Term, Status.A student can register at most once for the same course in the same term:
{StudentID, CourseCode, Term} -> RegistrationNo, Status.
The current instance is:
RegistrationNo | StudentID | CourseCode | Term | Status |
|---|---|---|---|---|
R9001 | S101 | DBMS | 2026S1 | ACTIVE |
R9002 | S101 | CN | 2026S1 | ACTIVE |
R9003 | S205 | DBMS | 2026S1 | ACTIVE |
R9004 | S205 | DBMS | 2026S2 | WAITLISTED |
Now compute attribute closure. {RegistrationNo}+ reaches RegistrationNo, StudentID, CourseCode, Term and Status, so it is a superkey. Its only proper subset is the empty set, which determines nothing under the declared dependencies. Therefore, {RegistrationNo} is a minimal, simple candidate key.
Next, {StudentID, CourseCode, Term}+ first gives RegistrationNo and Status. With RegistrationNo, it reaches all five attributes. It is a superkey. The rows demonstrate why all three two-attribute subsets fail:
{CourseCode, Term}is repeated by R9001 and R9003.{StudentID, Term}is repeated by R9001 and R9002.{StudentID, CourseCode}is repeated by R9003 and R9004.
Because every smaller subset sits inside one of those failing pairs, no proper subset is a superkey. The triple is therefore a minimal, composite candidate key.
Choose {RegistrationNo} as primary. Then {StudentID, CourseCode, Term} is both the alternate key and a composite candidate key. {RegistrationNo, StudentID} is a superkey, but not a candidate key, because removing StudentID leaves a key. Under the declared dependencies, there are exactly two candidate keys.

Enforce the chosen primary and alternate keys in SQL
The table definition can enforce both candidate keys:
CREATE TABLE Registration (
registration_no CHAR(5) NOT NULL,
student_id CHAR(4) NOT NULL,
course_code VARCHAR(10) NOT NULL,
term CHAR(6) NOT NULL,
status VARCHAR(12) NOT NULL,
CONSTRAINT pk_registration
PRIMARY KEY (registration_no),
CONSTRAINT uq_student_course_term
UNIQUE (student_id, course_code, term)
);PRIMARY KEY enforces the selected simple candidate key. The three-column UNIQUE constraint enforces the composite alternate key. NOT NULL on all four key attributes prevents null semantics from weakening the intended keys.
Predict each write before reading its outcome:
(R9005, S101, DBMS, 2026S1, ACTIVE)is rejected byuq_student_course_term, because R9001 already has that triple.(R9001, S333, OS, 2026S1, ACTIVE)is rejected by the primary key, because R9001 already exists.(R9005, S101, DBMS, 2026S2, ACTIVE)is allowed, because both candidate-key values are new.
UNIQUE does not mean less important, and it does not create a foreign key. Choosing a primary key identifies the designated key, but every alternate key must retain uniqueness too.
How exam-style questions test the distinction
Questions commonly ask you to derive all candidate keys from functional dependencies, classify a given set, or predict which constraint an insertion violates. Use the same rapid check every time:
RegistrationNoalone is a simple candidate key.{StudentID, CourseCode, Term}is a composite candidate key. It becomes alternate afterRegistrationNois selected.{RegistrationNo, CourseCode}is composite and unique, but not a candidate key. It is non-minimal becauseRegistrationNoalone is sufficient.
Try this timed prompt: if the primary key changes to {StudentID, CourseCode, Term}, what becomes alternate? The answer is {RegistrationNo}. Nothing becomes more or less unique. Only the selected-role labels swap.
For broader GATE CS preparation, connect these definitions to functional dependencies and normalization. Then use the GATE Test Series for constraint practice under test conditions.
Traps that lose marks and damage schemas
The first trap is, "Every unique multi-attribute set is a composite candidate key." {RegistrationNo, StudentID} disproves it: the set is unique, but RegistrationNo alone remains a key. Always test proper subsets.
The second trap is, "Primary keys must be single-column." A primary key can be composite because selection and attribute count are independent.
The third trap is treating alternate as nullable, duplicate, secondary or foreign. An alternate key is simply an unselected candidate key. It remains minimal and unique. Here, {StudentID, CourseCode, Term} is alternate, composite, and protected by UNIQUE plus NOT NULL.
Finally, do not infer a permanent key from four rows or add columns merely to force uniqueness. Start from business rules and dependencies. If a wider schema develops redundancy or update anomalies, study Normalization in DBMS: 1NF to BCNF next.
The short version and the next practice step
Every candidate key is a minimal superkey.
One candidate key is selected as primary.
The remaining candidate keys are alternate.
Composite means the key contains two or more attributes.
One key can therefore be both composite and alternate.
In the worked relation, RegistrationNo is the simple primary key, while {StudentID, CourseCode, Term} is the composite alternate key.
Use this solve order: write the declared dependencies, compute closures, remove attributes to prove minimality, list all candidate keys, then label the chosen primary and remaining alternate keys. Redo the example after swapping the primary-key choice, and predict the same three inserts again.
For domain, entity and referential constraints alongside keys, continue with Keys and Integrity Constraints in DBMS: Types, Rules and Worked Examples. That broader schema traces legal inserts and deletes across multiple integrity rules; the REGISTRATION relation above isolates attribute count versus candidate selection.
Keep learning
Advanced Recovery Algorithms in DBMS: ARIES Worked Step by Step
Work through one ARIES crash from checkpoint analysis to repeating history and logged undo. Every pageLSN, recLSN, loser transaction and CLR is traced.
Keys and Integrity Constraints in DBMS: One Cross-Concept Worked Example
Follow one college-registration database from functional dependencies to declared keys, then decide which of eight inserts and deletes preserve integrity.
DBMS Interview Questions: Keys, Normalisation and Transactions Through One Worked Schema
Build defensible DBMS interview answers by tracing keys, normal forms, ACID and isolation through one university-enrolment database.
Keys and Integrity Constraints in DBMS: Types, Rules and Worked Examples
Learn how superkeys, candidate keys, primary keys, foreign keys and integrity rules work together through one university database and a complete closure proof.





