Computer ScienceCBSE Class 12 · Database Concepts

Keys in a Relation: Candidate, Primary, Alternate and Foreign Key (Class 12 Computer Science)

Updated 17 October 2026 · Fact-checked

A key is an attribute (or set of attributes) that identifies tuples. Every attribute that could uniquely identify a tuple is a candidate key; the one chosen is the primary key; the remaining candidate keys are alternate keys. A foreign key is an attribute of one table that refers to the primary key of another table.

Understand Keys: Candidate, Primary, Alternate and Foreign Key

Candidate key. An attribute (or a group of attributes) that can uniquely identify every tuple of the relation and cannot be NULL for any tuple. A relation can have more than one candidate key.

Primary key. The candidate key chosen to identify tuples. It must be unique and not NULL, and a relation has only one primary key.

Alternate key. A candidate key that is not chosen as the primary key.

Foreign key. An attribute of one relation that refers to the primary key of another relation. It links the two tables. Its values must either be present in the primary key column of the referenced table or be NULL. Unlike a primary key, a foreign key may repeat.

Example 1: one table. TEACHER(TID, Email, Name, Subject): TID and Email are both unique and never NULL. So TID and Email are the candidate keys; choose TID as the primary key; Email is then the alternate key. Name is not a candidate key because two teachers may share a name.

Example 2: two tables. In the chapter tables STUDENT(RollNo, Name, Class, Phone, HouseID) and HOUSE(HouseID, HName, Captain), HouseID is the primary key of HOUSE. In STUDENT it is a foreign key: it refers to HOUSE, and the same HouseID (H1) occurs in two student rows. Phone in STUDENT contains a NULL (Joy), so it cannot be a primary key.

Why a foreign key matters. It prevents an invalid link. A student cannot be placed in house H9 if HOUSE has no H9; a house also cannot be removed while students still refer to it (this rule is called referential integrity).

Keys: Candidate, Primary, Alternate and Foreign Key: key facts

Key types: Candidate key can uniquely identify tuples, no NULL
Primary key the chosen candidate key, only one
Alternate key candidate keys not chosen
Foreign key refers to the primary key of another table; Alternate = candidate minus primary.
Rules of a primary key: Unique values
Never NULL
Only one per relation
May have more than one attribute; Values never repeat.
Rules of a foreign key: Refers to the primary key of another table
Values can repeat
May be NULL
Must exist in the referenced table; Links two tables.
Counting: Alternate keys = candidate keys minus 1 | Primary keys per relation = 1; Only one candidate becomes primary.

Candidate key	can uniquely identify tuples, no NULL
Primary key	the chosen candidate key, only one
Alternate key	candidate keys not chosen
Foreign key	refers to the primary key of another table

How to answer Keys: Candidate, Primary, Alternate and Foreign Key questions

Use this routine for key identification questions.

1List attributes whose values are unique for every row and never NULL: these are the candidate keys.
2Choose the best one as primary (usually a simple code such as an ID); the rest are alternate keys.
3Check the second table: an attribute that is the primary key there and appears here is a foreign key.
4Write the key with its table: HouseID in STUDENT references HOUSE.
5Test a value insertion against the foreign key: its value must exist in the parent table, or the insertion fails.

Keys: Candidate, Primary, Alternate and Foreign Key in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Roll number and admission number

Suppose a school registers each student with a roll number and a unique admission number, both always filled.

Which attributes are candidate keys, and how are the primary and alternate keys decided?

Try it first, then show the solutionSolution

Both are unique and never NULL, so both are candidate keys.
The school picks roll number as primary key; admission number becomes the alternate key.

Answer: Candidate keys: roll number and admission number. Primary: roll number. Alternate: admission number.

Memory hook: Two ID cards, one is used daily and the other is spare.

A class and its teacher

Suppose a CLASS table has ClassID as primary key and a TEACHER table has a ClassTeacherOf attribute holding ClassID values.

Which attribute is the foreign key and what does it ensure?

Try it first, then show the solutionSolution

ClassTeacherOf in TEACHER refers to ClassID in CLASS: it is the foreign key.
It ensures that every teacher is linked to a class that exists.

Answer: ClassTeacherOf is the foreign key.

Memory hook: A foreign key is a pointer to the home table.

Tricks to remember Keys: Candidate, Primary, Alternate and Foreign Key

Short memory aids. Say each one aloud once, then use it on a question the same day.

Candidates, one wins
All candidates compete; the winner is primary and the rest are alternates.
Use it when: Candidate, primary, alternate.
Primary is unique, foreign visits
A primary key lives in its own table; a foreign key visits from another table.
Use it when: Foreign key definition.
Foreign key may repeat
Many students can have the same HouseID, so the foreign key repeats.
Use it when: Foreign key versus primary key.
No NULL in primary
A primary key is never NULL.
Use it when: Choosing the primary key.

Keys: Candidate, Primary, Alternate and Foreign Key: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: Primary key property · 1 mark · MCQBoard level

A primary key can have:
(a) NULL values (b) duplicate values (c) neither NULL nor duplicate values (d) only text values

Try it first, then show the solutionSolution

A primary key must be unique and not NULL.

Answer: (c) neither NULL nor duplicate values

Example 2: Candidate and primary · 2 marks · Short answerBoard level

Differentiate between a candidate key and a primary key.

Try it first, then show the solutionSolution

A candidate key is any attribute or set of attributes that can uniquely identify tuples; a relation may have several. (1 mark)
The primary key is the one candidate key chosen to identify tuples; there is only one per relation. (1 mark)

Answer: Candidate: all possible unique identifiers. Primary: the one chosen.

Example 3: Identify the keys · 3 marks · Short answerHarder board level

TEACHER(TID, Email, Name, Subject) has unique and non-NULL values in TID and Email, while Name and Subject repeat. Write the candidate, primary and alternate keys.

Try it first, then show the solutionSolution

Candidate keys: TID and Email. (1 mark)
Primary key: TID (a compact code). (1 mark)
Alternate key: Email. (1 mark)

Answer: Candidates TID and Email; primary TID; alternate Email

Example 4: Foreign key · 3 marks · Short answerHarder board level

In STUDENT(RollNo, Name, Class, Phone, HouseID) and HOUSE(HouseID, HName, Captain): (i) name the foreign key and the table it refers to; (ii) will INSERT of a student with HouseID H9 succeed if HOUSE has H1, H2 and H3 only? Give a reason.

Try it first, then show the solutionSolution

(i) HouseID in STUDENT is the foreign key; it refers to the primary key HouseID of HOUSE. (1 mark)
(ii) The insert is rejected (when the foreign key is defined), because H9 does not exist in HOUSE. (1 mark)
Reason: a foreign key value must match a primary key value in the referenced table or be NULL. (1 mark)

Answer: (i) HouseID refers to HOUSE (ii) Rejected: H9 is not present in HOUSE

Example 5: Case: library tables · 4 marks · Case-basedHOTS / competency

Suppose a library has BOOK(ISBN, Title), MEMBER(MID, Name, Email) and ISSUE(IssueID, MID, ISBN, IssueDate). MID and Email are unique and non-NULL in MEMBER.
(i) Name the primary key of ISSUE. (1 mark)
(ii) Name the foreign keys in ISSUE and the tables they refer to. (1 mark)
(iii) Name the candidate keys of MEMBER and its alternate key (primary key is MID). (1 mark)
(iv) Can one member appear in many ISSUE rows? Which key property allows this? (1 mark)

Try it first, then show the solutionSolution

(i) IssueID, which identifies each issue uniquely.
(ii) MID refers to MEMBER; ISBN refers to BOOK.
(iii) Candidate keys: MID and Email; alternate key: Email.
(iv) Yes. MID in ISSUE is a foreign key, and a foreign key can repeat; it need not be unique.

Answer: (i) IssueID (ii) MID to MEMBER, ISBN to BOOK (iii) MID, Email; alternate Email (iv) Yes, a foreign key can repeat

Where students lose marks in Keys: Candidate, Primary, Alternate and Foreign Key

The slips examiners see most often, and what to write instead.

Mistake: Saying a primary key can be NULL
A primary key must identify every tuple, so NULL is not allowed.
Wrong working
Primary key may be NULL once
Right working
Primary key is never NULL
Do this: Write "unique and not NULL" for the primary key.
Usually costs: 1 mark
Mistake: Calling every unique column a candidate key
A candidate key must also be non-NULL for every tuple.
Wrong working
Phone (with a NULL) is a candidate key
Right working
Phone with a NULL cannot be a candidate key
Do this: Check both unique and non-NULL.
Usually costs: 1 mark
Mistake: Reversing the foreign key direction
The foreign key sits in the child table and refers to the primary key of the parent.
Wrong working
HouseID in HOUSE is the foreign key
Right working
HouseID in STUDENT is the foreign key referring to HOUSE
Do this: State the table the key sits in and the table it refers to.
Usually costs: 1 mark

Exam tips

Always write the key as attribute (table) in case studies, and name the table it refers to.
Define: a candidate key is a possible primary key; alternate keys are the candidates not chosen.
Check the NULLs in the data before declaring a candidate key.
A foreign key may repeat or be NULL; a primary key cannot.
The number of alternate keys is the number of candidate keys minus one.

Practice questions on Keys: Candidate, Primary, Alternate and Foreign Key

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

Q1 · 1 mark · MCQBoard level
A foreign key in one table refers to the ______ of another table.
(a) foreign key (b) primary key (c) any attribute (d) tuple
Hint
Think of the parent table.
Answer
(b) primary key
Q2 · 1 mark · Very short answerBoard level
How many primary keys can a relation have?
Hint
Only one chosen.
Answer
Only one (though it may consist of more than one attribute).
Q3 · 2 marks · Short answerHarder board level
A table has three candidate keys. How many alternate keys does it have if one of them is chosen as the primary key? Name the roles.
Hint
Candidate minus primary.
Answer
Two alternate keys. One candidate becomes the primary key; the other two are alternate keys.
Q4 · 1 mark · Assertion-reasonHarder board level
Assertion (A): A foreign key column can contain repeated values.
Reason (R): Many tuples of one table can refer to the same tuple of the other table.
(a) Both A and R are true and R is the correct explanation of A. (b) Both A and R are true but R is not the correct explanation of A. (c) A is true but R is false. (d) A is false but R is true.
Hint
Think of many students in one house.
Answer
(a) Both are true and R explains A.
Q5 · 3 marks · Short answerHOTS / competency
Suppose a table PHONEBOOK(PID, Name, Mobile, City) has a unique, non-NULL PID. Mobile values are unique, but a few are NULL for new contacts. A student proposes Mobile as the primary key. Is that acceptable? Which key should be primary, and can Mobile be an alternate key?
Hint
A primary key must never be NULL.
Answer
No: Mobile contains NULL, so it cannot be a primary key (and, as it is not always filled, cannot be a candidate key). PID should be the primary key. Mobile is not a candidate key, so it cannot be an alternate key either.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1.Which key uniquely identifies tuples and is chosen by the designer?Show answer
Primary key.
2.What is an alternate key?Show answer
A candidate key that is not chosen as the primary key.
3.What does a foreign key refer to?Show answer
The primary key of another table.
4.Can a primary key be NULL?Show answer
No.
5.Can a foreign key repeat?Show answer
Yes.
6.What is a candidate key?Show answer
An attribute or set of attributes that can uniquely identify tuples.

Keys: Candidate, Primary, Alternate and Foreign Key: frequently asked questions

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies tuples in its own table and cannot be NULL or repeat. A foreign key is a column in another table that refers to that primary key; it may repeat and may be NULL.

What are candidate and alternate keys?

Candidate keys are all attributes that could serve as the primary key. The chosen one is the primary key; the rest are alternate keys.

Can a table have more than one primary key?

No. A table has only one primary key, although the key may be made up of more than one attribute.

Why do we use foreign keys?

They connect tables and make sure that a value used in one table, such as a HouseID, exists in the table it refers to.