DatabasesLesson 1 of 1 in the course Database design and normalization

Foreign Keys, Referential Integrity, and Referential Actions

5 flashcards · 5 quiz questions · 5 match pairs · 5 fill-in sentences · English

Save course
Lesson progress
0 / 5

Your progress is kept in this browser only.Sign up to save it

Summary

A foreign key connects records in one table to records in another. This lesson introduces foreign keys, candidate keys, and the integrity rule that keeps references valid.

A foreign key is one or more attributes (columns) in a table whose values refer to a candidate key in another table. It establishes a link between the rows of the two tables.

For example, a TEAM table might contain MEMBER_NAME, which refers to PERSON_NAME in a PERSON table. If MEMBER_NAME is a foreign key, every non-null member name in TEAM must also occur as a valid person name in PERSON.

All flashcards

Foreign key
One or more attributes in a table whose values refer to a candidate key in a referenced table.
Composite foreign key
A foreign key made of multiple columns that correspond to a candidate key in the referenced table.
Self-referencing foreign key
A foreign key that refers to a candidate key in the same table.
Normalization and foreign keys
Normalization reduces repetition by organizing data into related tables; foreign keys preserve links between them.
CASCADE
A referential action that propagates a parent deletion or key update to matching child rows.

Quiz questions

  1. With ON DELETE CASCADE, what happens to child rows that reference a deleted parent row?Answer: The referencing child rows are deleted as well.
  2. What does the RESTRICT action do when child rows still reference a parent row targeted for deletion?Answer: It rejects the deletion while those references exist.
  3. How does NO ACTION differ from RESTRICT when a parent change leaves a foreign-key violation?Answer: NO ACTION checks at the end of the statement; RESTRICT checks before the operation.
  4. What must be true of a child foreign-key column for the SET NULL action to work?Answer: The column must permit NULL values.
  5. When can SET DEFAULT preserve referential integrity after a parent row is deleted?Answer: When the assigned default value satisfies the foreign-key constraint.

Based on the Wikipedia article Foreign key, licensed CC BY-SA 4.0. Adapted into a summary, flashcards and quizzes.

More in Databases

All Databases lessons
DatabasesSQL Foundations: Language Model, History, Types, and Semantics5 flashcards · 5 quiz questions · 4 gamesPlay free →DatabasesDatabase Query Optimization: Plans, Costs, and Estimation5 flashcards · 5 quiz questions · 4 gamesPlay free →DatabasesDatabase Index Structures, Query Patterns, and Trade-Offs5 flashcards · 5 quiz questions · 4 gamesPlay free →DatabasesDatabase Transaction Isolation: Anomalies, Levels, and Concurrency Control5 flashcards · 5 quiz questions · 4 gamesPlay free →DatabasesSQL Joins: Matching, Row Preservation, and Execution5 flashcards · 5 quiz questions · 4 gamesPlay free →