Keys and Integrity Constraints in the Relational Model
1. Model Keys in the Relational Model
Keys are used to uniquely identify tuples and to establish relationships between relations.
Super Key
A Super Key is a set of one or more attributes that uniquely identifies a tuple in a relation.
- It may contain extra attributes
- Uniqueness is guaranteed, but minimality is not
Example:
STUDENT (Roll_No, Email, Name)
Super Keys:
- {Roll_No}
- {Email}
- {Roll_No, Name}
Candidate Key
A Candidate Key is a minimal super key.
- No attribute can be removed
- Multiple candidate keys can exist
Example:
STUDENT (Roll_No, Email, Name)
Candidate Keys:
- {Roll_No}
- {Email}
Primary Key
A Primary Key is one candidate key chosen to uniquely identify tuples.
Rules:
- Must be unique
- Cannot be NULL
- Only one primary key per relation
Example:
STUDENT (Roll_No, Name, Age)
Primary Key → Roll_No
Alternate Key
An Alternate Key is a candidate key not selected as the primary key.
Example:
Primary Key → Roll_No
Alternate Key → Email
Alternate keys still enforce uniqueness.
Foreign Key
A Foreign Key is an attribute in one relation that references the primary key of another relation.
Purpose:
- Establish relationships
- Maintain referential integrity
Example:
ENROLLMENT (Student_ID, Course_ID)
Student_ID → references STUDENT(Student_ID)
Composite Key
A Composite Key is a key formed using more than one attribute to uniquely identify a tuple.
Example:
ENROLLMENT (Student_ID, Course_ID)
Composite Primary Key → {Student_ID, Course_ID}
Used when no single attribute is sufficient.
Compound Key
A Compound Key is often used interchangeably with a Composite Key.
Conceptually:
- Both consist of multiple attributes
- Some textbooks differentiate compound keys as multi-attribute keys formed from simple attributes
For DBMS exams:
- Treat Composite Key = Compound Key
Surrogate Key
A Surrogate Key is an artificially generated key with no business meaning.
Characteristics:
- System-generated (Auto Increment, UUID)
- Used when no natural key exists, or natural keys are large
Example:
Employee_ID (Auto Increment)
Surrogate keys simplify indexing and improve performance.
Summary of Key Types
| Key Type | Description |
| Super Key | Ensures uniqueness |
| Candidate Key | Minimal super key |
| Primary Key | Chosen candidate key |
| Alternate Key | Remaining candidate keys |
| Foreign Key | References another table |
| Composite Key | Multiple attributes |
| Compound Key | Same as composite (exam-safe) |
| Surrogate Key | Artificial system-generated key |
2. Integrity Constraints in the Relational Model
Integrity constraints are rules enforced on data to ensure that the database remains accurate, consistent, and reliable throughout its lifetime.
They prevent invalid data from being inserted, updated, or deleted.
Domain Constraint
A Domain Constraint restricts the set of values that an attribute can take.
It is based on:
- Data type
- Range of values
- Format or pattern
Purpose:
To ensure that attribute values are meaningful and valid.
Example:
Age → INTEGER, Age > 0
Gender → {‘M’, ‘F’, ‘Other’}
Email → valid email format
If a value violates the domain constraint, the operation is rejected.
Entity Integrity Constraint
The Entity Integrity Constraint ensures that:
The primary key attribute of a relation cannot be NULL
Reason:
- Every tuple represents a distinct real-world entity
- If the primary key is NULL, the tuple cannot be uniquely identified
Example:
STUDENT (Student_ID, Name, Age)
Student_ID cannot be NULL
This constraint guarantees that each row is identifiable.
Referential Integrity Constraint
The Referential Integrity Constraint governs the relationship between two relations.
It states that:
A foreign key value in a child relation must either
- Match an existing primary key value in the parent relation, or
- Be NULL
Why Referential Integrity is Needed
Without this constraint:
- Invalid references can exist
- Relationships between tables break
- Data becomes inconsistent
Example
STUDENT
(Student_ID, Name)
ENROLLMENT
(Student_ID, Course_ID)
Here:
- Student_ID in ENROLLMENT is a foreign key
- It references Student_ID in STUDENT (primary key)
Valid case:
ENROLLMENT.Student_ID = 101
STUDENT.Student_ID = 101 exists.
Invalid case (violation):
ENROLLMENT.Student_ID = 999
No such Student_ID in STUDENT
What Referential Integrity Prevents
- Orphan records
A child tuple referencing a non-existent parent - Invalid references
Foreign key values that do not correspond to any primary key
Referential Actions
When a referenced primary key is:
- Deleted
- Updated
The DBMS can:
- Restrict the operation
- Cascade the change
- Set foreign key toNULLL
Key Constraints
In relational databases, key constraints are implemented using SQL constraints.
These constraints are enforced by the DBMS to maintain data integrity and consistency.
NOT NULL Constraint
The NOT NULL constraint ensures that an attribute cannot have NULL values.
Purpose:
- Prevents missing or undefined data
- Supports entity integrity
Example:
Student_ID NOT NULL
Key Relation:
- Mandatory for Primary Keys
UNIQUE Constraint
The UNIQUE constraint ensures that no two tuples have the same value for an attribute.
Purpose:
- Enforces key constraint
- Prevents duplicate values
Example:
Email UNIQUE
Key Relation:
- Used for Candidate Keys
- Used for Alternate Keys
DEFAULT Constraint
The DEFAULT constraint assigns a default value when no value is provided.
Purpose:
- Avoids NULL values
- Ensures meaningful initial data
Example:
Status DEFAULT 'Active'
Key Relation:
- Not a key constraint directly
- Supports domain and data consistency
CHECK Constraint
The CHECK constraint restricts values based on a logical condition.
Purpose:
- Enforces domain constraints
- Maintains valid data ranges
Example:
Age CHECK (Age >= 18)
PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each tuple in a relation.
Properties:
- Enforces UNIQUE
- Enforces NOT NULL
- Only one primary key per table
Example:
Student_ID PRIMARY KEY
Key Relation:
- Implements Entity Integrity
- Implements Primary Key Constraint
FOREIGN KEY Constraint
The FOREIGN KEY constraint enforces referential integrity between relations.
Rules:
- Value must exist in the referenced primary key
- Can be NULL (unless restricted)
Example:
Student_ID REFERENCES STUDENT(Student_ID)
Key Relation:
- Implements Referential Integrity Constraint
Summary
• Super, candidate, and primary keys uniquely identify tuples
• Alternate keys provide additional unique identifiers
• Foreign keys establish relationships between relations
• Composite and compound keys use multiple attributes
• Surrogate keys are system-generated identifiers
• Domain constraints restrict valid attribute values
• Entity integrity ensures primary keys are not NULL
• Referential integrity maintains valid table relationships
• Key constraints prevent duplicate records