• Practice
  • About 12 min
  • 5 practice questions

Key identification drills: candidate, primary, alternate and foreign

Pick out candidate, primary, alternate, composite and foreign keys from table descriptions and count degree, cardinality and superkeys.

If something looks wrong, please tell us.

Keys are among the most reliably tested ideas in database papers. The questions are short, so every term must be exact. This lesson gives a one-line meaning for each, then drills that mix them.

One-line meanings

Key terms
TermMeaning
Candidate keyA column or set of columns that could uniquely identify each row (minimal)
Primary keyThe candidate key chosen to identify rows; unique and never NULL
Alternate keyA candidate key that was not chosen as the primary key
Composite keyA key made of two or more columns together
Foreign keyA column that refers to the primary key of another table
SuperkeyAny set of columns that uniquely identifies rows; it may contain extra columns
DegreeNumber of columns (attributes)
CardinalityNumber of rows (tuples)

Drills with full working

Drill 1. TEACHER(TID, Name, Email, Phone, SubjectCode). TID and Email are both unique for every teacher. Name is not unique. Identify the keys. Candidate keys: TID and Email. If TID is chosen as primary, Email is the alternate key. SubjectCode, if it refers to a SUBJECT table, is a foreign key. The degree is 5.

Drill 2. ENROL(StudentID, CourseID, Grade). A student can join many courses, and a course has many students, but one student joins a course once. Which key is needed? Neither StudentID nor CourseID alone is unique, but the pair is. The primary key is the composite key (StudentID, CourseID). Each of the two is also a foreign key pointing to the STUDENT and COURSE tables.

Drill 3. A table has 40 rows and 6 columns. Its degree is 6 and its cardinality is 40. If 5 more rows are inserted and 1 column is added, the degree becomes 7 and the cardinality 45.

Drill 4 (counting superkeys). Table R(A, B, C) has only one candidate key, A. The superkeys are all sets that contain A: {A}, {A, B}, {A, C}, {A, B, C}. That is 4, which is 2 to the power 2, since each of the other two columns may be included or not.

Drill 5. Now R(A, B, C) has two candidate keys, A and B. A set is a superkey if it contains A or B. All subsets of three columns number 2 to the power 3 = 8. Those with neither A nor B are the empty set and {C}, which is 2. So the superkeys number 8 - 2 = 6: {A}, {B}, {A,B}, {A,C}, {B,C}, {A,B,C}.

Drill 6. Which of these is the weakest choice for a primary key of a PERSON table: a unique registration number, Name, or an AutoNumber ID? Name, because two persons can share a name. The system-generated ID is usually the best because it is unique and never changes.

A primary key is unique and never NULL. Foreign keys may repeat and may be NULL when the rule allows it. Degree counts columns; cardinality counts rows.

— Remember

Typical traps

  • Thinking a table can have several primary keys. It has exactly one; others are alternate keys.
  • Thinking a foreign key must be unique in its own table. It usually repeats.
  • Mixing degree with cardinality.
  • Choosing a changing value, such as an address, as a key.

Key takeaways

  • Candidate keys compete for the primary key; the rest are alternate keys.
  • A composite key uses several columns together.
  • A foreign key links to the primary key of another table.

Check your understanding

  1. 1. A table has 12 rows and 5 columns. Its cardinality is

    1. 5
    2. 17
    3. 12
    4. 60
    Show answer

    Answer: C. Cardinality means the number of rows, which is 12. The degree is 5.

  2. 2. A candidate key not chosen as primary key is called

    1. Foreign key
    2. Alternate key
    3. Composite key
    4. Super table
    Show answer

    Answer: B. Candidate keys that are not made primary are alternate keys.

  3. 3. In ENROL(StudentID, CourseID, Grade), the primary key is

    1. StudentID, taken alone
    2. Grade, taken alone
    3. CourseID, taken alone
    4. StudentID with CourseID
    Show answer

    Answer: D. Only the combination is unique for each row, so a composite key is needed.

  4. 4. Table R(A, B, C) has A as its only candidate key. How many superkeys does it have?

    1. 4
    2. 1
    3. 2
    4. 8
    Show answer

    Answer: A. A superkey must contain A; B and C are each optional, giving 2 x 2 = 4 sets.

  5. 5. A foreign key

    1. Must stay unique in its table
    2. Is always left as NULL
    3. Is the same as the degree
    4. Points to another table's key
    Show answer

    Answer: D. A foreign key stores values that match the primary key of a related table.

Privacy · Terms · Refunds · Delivery