Primary and foreign keys
Candidate key, primary key, composite key, natural or artificial key; foreign key and referential integrity: what the database accepts and refuses on insertion, update and deletion.
- 27 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
Candidate key: what is it?
Show the answer
A minimal set of columns whose values identify each row of the table: none can be removed without losing that property.
In STUDENT(num, ine, email, name), num, ine and email can each play this role if they are unique. (name) is not one: two students can share a name.
ENROLMENT(student, module, year, mark). A student can retake a module another year, but never twice in the same year. What is the primary key?
- (student, module, year)
- (student, module)
- (student, module, year, mark)
- (student, year)
Show the answer
(student, module, year)
With (student, module), a retake would give two rows with the same key. With (student, year), two modules in the same year would collide. Adding the mark would make the key non-minimal: (student, module, year) is already enough.
A primary key never contains … and is never … from one row to another.
Show the answer
A primary key never contains NULL and is never repeated from one row to another.
A row whose identity is unknown or shared can no longer be designated with certainty by foreign keys.
Primary key: what is it?
Show the answer
The candidate key chosen to identify rows; it is never repeated, never empty, and there is only one per table.
The other candidate keys remain possible as alternate keys, under a uniqueness constraint. The primary key is the one other tables reference.