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.
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 → Ymeans rows agreeing onXmust also agree onY. - 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
Worked example
- In
Employees(employee_id, email, team_id, team_name), the attributeemployee_idmay identify each row uniquely. - If
team_idalways determinesteam_name, thenteam_id → team_nameis a functional dependency. - Repeating
team_namein 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 it
- 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.