Teletalk Assistant Manager (IT) Exam: 2023প্রশ্ন ও সমাধান

Direct answer: a candidate key is any minimal set of attributes that can uniquely identify a row; a primary key is the one candidate key chosen to be the table's official identifier; a foreign key is an attribute in one table that references the primary key of another table, enforcing a relationship between them.

Key type	Defined in	Purpose	Can repeat?	Can be NULL?
Candidate key	Same table	Any column(set) that could serve as unique identifier	No (must be unique)	No
Primary key	Same table	The chosen candidate key that officially identifies each row	No	No
Foreign key	A different (referencing) table	Links a row to a row in another table	Yes (many rows can reference the same parent)	Yes (if the relationship is optional)

Worked example:

erDiagram
  STUDENT ||--o{ ENROLLMENT : has
  COURSE  ||--o{ ENROLLMENT : has
  STUDENT {
    int roll_no PK
    string email
    string name
  }
  COURSE {
    int course_id PK
    string title
  }
  ENROLLMENT {
    int roll_no FK
    int course_id FK
    string grade
  }

• In STUDENT, both roll_no and email could uniquely identify a student — both are candidate keys; roll_no is chosen as the primary key.
• In ENROLLMENT, roll_no and course_id are foreign keys, each referencing the primary key of STUDENT and COURSE respectively, and together they typically form ENROLLMENT's own composite primary key.

Key relationships:

• Every primary key is a candidate key, but not every candidate key is chosen as the primary key (the unchosen ones are called alternate keys).
• A foreign key's values must either be NULL or match an existing primary-key value in the referenced table — this is the referential integrity constraint.

Exam takeaway: candidate = "could be the key"; primary = "the chosen one, enforced unique + NOT NULL"; foreign = "points to another table's primary key to model a relationship."