Relational Schema, Constraints & Keys
The relational model represents a database as a collection of mathematically structured relations (tables) containing tuples (rows) and attributes (columns).
To ensure data correctness during insertion, update, and deletion operations, the database engine enforces several core integrity constraints.
Core Integrity Constraints
| Relational Constraint | Core Structural Rule | Engine Enforcement Mechanism |
|---|---|---|
| Domain Constraint | Attribute values must be atomic and match the defined data type. | Rejects values of incorrect data types or those failing check constraints. |
| Tuple Uniqueness | Every row within a relation must be unique. | Blocks insertion of duplicate rows. |
| Key Constraint | Primary key values must be unique across all records. | Rejects updates or inserts that duplicate existing key values. |
| Entity Integrity | Primary key columns cannot contain null values. | Rejects insertions of NULL values into primary key fields. |
| Referential Integrity | Foreign keys must match an existing primary key or be null. | Restricts, cascades, or sets null on parent record modifications. |
Database Keys
Keys are sets of attributes that uniquely identify a tuple within a relation. Relational theory classifies keys into several types based on their minimality and role in schema design.
- Super Key: Any set of one or more attributes that collectively identify each tuple uniquely. A super key can include redundant attributes.
- Candidate Key: A minimal super key. It is a set of attributes that uniquely identifies tuples, where removing any attribute destroys its uniqueness.
- Primary Key: The specific candidate key selected by the database designer to uniquely identify tuples in a relation. Primary key values must be unique and cannot be null.
- Alternate Key: Any candidate keys that were not selected as the primary key. They still enforce uniqueness.
- Foreign Key: An attribute in a referencing relation whose values depend on and match the primary key values in a referenced relation. It establishes relationships between tables.
- Composite Key: A primary key comprised of multiple attributes (columns) rather than a single column.
- Unique Key: Similar to a primary key in that it enforces unique values, but it can accept a single null value (or multiple, depending on the specific SQL dialect).
Placement Takeaways: Keys & Constraints
Keys and constraints form the foundation of most basic SQL and DBMS interview questions. Interviewers frequently ask candidates to differentiate between highly similar key types.
Pro Tip: Super Key vs Candidate Key
Always remember: All candidate keys are super keys, but not all super keys are candidate keys! A Candidate Key is minimal, meaning if you remove even one attribute from it, it loses its ability to uniquely identify a row.
Example: Keys in a Student Table
Consider a relation: Student(Roll_No, Email, Name, Age).
Student
| Roll_No | Name | Age | |
|---|---|---|---|
| 101 | [email protected] | Alice | 20 |
| 102 | [email protected] | Bob | 21 |
| 103 | [email protected] | Charlie | 20 |
- Super Keys: {Roll_No}, {Email}, {Roll_No, Name}, {Email, Age} (All uniquely identify a student).
- Candidate Keys: {Roll_No} and {Email}. (They are minimal super keys).
- Primary Key: The designer chooses {Roll_No} as the PK.
- Alternate Key: {Email} becomes the alternate key since it wasn't selected as PK.
- Unique Key: We enforce a Unique constraint on {Email} to ensure no duplicates.
Referential Integrity Cascade
Be prepared to explain
ON DELETE CASCADE. If a record in the parent table is deleted, referential integrity rules determine what happens to child records. Cascading deletes the child records automatically to prevent orphaned data.