Relations / Sql Keys Constraints
Least You Need to Know: Keys, Functional Dependencies, and Constraints
Database design questions are relation questions wearing engineering clothes. Keys identify tuples, foreign keys connect relations, and functional dependencies explain when one set of attributes determines another.
The least you need to know
- A candidate key uniquely identifies a row and is minimal: no proper subset still uniquely identifies rows.
- A primary key is one chosen candidate key used as the main row identifier.
- A foreign key enforces that referenced values correspond to existing rows in another table.
- A functional dependency `X → Y` means rows agreeing on `X` must also agree on `Y`.
- Update and insertion anomalies often signal that a relation is mixing facts that should be separated.
Key notation
X → Y
attribute set X functionally determines attribute set Y
PK
primary key
FK
foreign key referencing another relation's key
Tiny worked example
- In `Employees(employee_id, email, team_id, team_name)`, the attribute `employee_id` may identify each row uniquely.
- If `team_id` always determines `team_name`, then `team_id → team_name` is a functional dependency.
- Repeating `team_name` in every employee row can create update anomalies if the team name changes.
- Splitting team facts into a separate `Teams(team_id, team_name)` relation removes that repetition.
Common mistakes
- Uniqueness alone is not enough for a candidate key; minimality matters too.
- Students often mix up 'references another row' with 'is unique within this table'.
- Functional dependencies are about determined values, not about arithmetic formulas.
How to recognize this kind of problem
- When the prompt asks what uniquely identifies each row, think candidate keys first.
- When repeated descriptive data changes in many rows at once, think anomaly and normalization.
- When one attribute value fixes another across all rows, that is functional-dependency language.
Next recommended lesson
Try Least You Need to Know: Relations in Databases and State Machines next for a nearby refresher.
Least You Need to Know: Relations in Databases and State MachinesRelated lessons
Keep going with nearby lessons in the same topic.