8.1.3 Keys, Relationships and Data Integrity
Relational tables become useful when each tuple can be identified reliably and related data can be connected without unnecessary duplication. This lesson explains how keys identify tuples, how relationships connect tables and how the DBMS protects the consistency of those connections.
By the end of this section, you should be able to:
- Distinguish between primary, candidate, secondary and foreign keys.
- Explain when a key needs more than one attribute.
- Recognise one-to-one, one-to-many and many-to-many relationships.
- Explain how foreign keys support referential integrity.
- Describe how primary-key rules contribute to data integrity.
- Explain how an index can speed up retrieval and identify its costs.
Why Tables Need Reliable Identifiers
Imagine a network of environmental sensors. Several sensors could have the same model name, be installed in the same habitat or report the same reading. None of those values reliably identifies one particular sensor.
| Possible identifier | Suitable? | Reason |
|---|---|---|
| ModelName | No | Many sensors may use the same model. |
| HabitatID | No | Several sensors can be installed in one habitat. |
| SensorID | Yes | Each sensor is assigned one distinct, permanent identifier. |
| SerialNumber | Potentially | It can identify a sensor if uniqueness and presence are guaranteed. |
A good identifying key must be unique for every tuple and must have a value whenever a tuple exists. Designers also prefer values that are stable and unlikely to change.
Primary, Candidate and Secondary Keys
A table may have more than one possible unique identifier. These possible identifiers are candidate keys. The designer selects one of them as the table's main identifier.
Primary key: the candidate key selected as the table's main identifier.
Secondary key: in this syllabus context, a candidate key that was not selected as the primary key.
| Attribute in Sensor | Key status | Explanation |
|---|---|---|
| SensorID | Primary key | The database uses this generated code as its main identifier. |
| SerialNumber | Secondary key | It is also unique, but it was not selected as the primary key. |
| NetworkAddress | Not necessarily a candidate key | An address could be reassigned or temporarily absent. |
| ModelName | Not a candidate key | Duplicate model names are expected. |
The DBMS rejects a duplicated primary-key value. It also prevents a tuple from being stored without the required primary-key value. These rules ensure that every stored tuple can be referenced unambiguously.
When One Attribute Is Not Enough
Some tables cannot be identified using one attribute alone. In that case, two or more attributes can be combined to form a compound key, also called a composite key.
The Reading relation stores measurements collected by sensors:
| SensorID | ReadingTime | Value | Unit |
|---|---|---|---|
| S-041 | 2027-08-18 09:00 | 18.7 | °C |
| S-041 | 2027-08-18 09:15 | 18.9 | °C |
| S-073 | 2027-08-18 09:00 | 64 | % |
SensorID repeats because one sensor produces many readings. ReadingTime also repeats because different sensors can report at the same moment. The combination (SensorID, ReadingTime) can be unique even though neither component is unique by itself.
Foreign Keys Connect Related Tables
The sensor database stores habitats separately so that habitat details do not need to be repeated for every sensor.
Sensor(SensorID, SerialNumber, ModelName, HabitatID)
HabitatID is the primary key in Habitat. The same attribute appears in Sensor as a foreign key, allowing each sensor tuple to refer to its habitat.
| Table | HabitatID role | Uniqueness |
|---|---|---|
| Habitat | Primary key | Each HabitatID appears once. |
| Sensor | Foreign key | The same HabitatID may appear in several sensor tuples. |
Relationship Types
A relationship describes how instances represented by two tables are associated. Cardinality states how many instances can participate on each side.
| Relationship | Meaning | Original example | Typical implementation idea |
|---|---|---|---|
| One-to-one (1:1) | One instance on each side is associated with at most one on the other side. | One sensor has one current installation certificate. | A unique foreign key can connect the two tables. |
| One-to-many (1:M) | One instance on the first side can be associated with many on the second. | One habitat contains many sensors. | The foreign key is stored at the many side. |
| Many-to-many (M:M) | Many instances on either side can be associated with many on the other. | Researchers participate in several projects, and projects involve several researchers. | A link table stores the associations. |
Resolving a many-to-many relationship
A single foreign-key cell cannot contain a list of several references. A link table can represent each pairing as a separate tuple:
Project(ProjectID, Title)
ResearcherProject(ResearcherID, ProjectID, Role)
ResearcherProject contains two foreign keys. Their combination can also form its compound primary key. Detailed construction and documentation of these relationships using E–R diagrams is developed in Section 8.1.4.
Referential Integrity
Referential integrity prevents foreign-key values from referring to tuples that do not exist. Before accepting a sensor with HabitatID H-09, the DBMS checks that H-09 is already present as a primary-key value in Habitat.
| Attempted operation | DBMS response | Integrity reason |
|---|---|---|
| Add Sensor S-088 with HabitatID H-02, which exists. | Accept | The foreign key has a valid parent tuple. |
| Add Sensor S-089 with HabitatID H-99, which does not exist. | Reject | The operation would create an orphan reference. |
| Delete Habitat H-02 while sensors still reference it. | Reject or apply a defined update rule | Existing sensor tuples must not be left pointing to nothing. |
| Add another sensor with primary key S-041. | Reject | Primary-key values must remain unique. |
These constraints improve structural consistency, but they cannot prove that a value is factually correct. A valid HabitatID may still have been chosen for the wrong habitat.
Indexing for Faster Retrieval
Searching every tuple in a large table can be slow. A DBMS can create an index containing selected search values together with references to the corresponding tuples.
| Without an index | With an index |
|---|---|
| The DBMS may inspect tuples one after another until a match is found. | The DBMS uses the organised lookup structure to locate matching tuples more quickly. |
| No extra index structure needs to be stored. | Extra storage is required for the index. |
| Inserting or updating a tuple changes only the table data. | The DBMS may also need to update one or more indexes. |
Primary keys are commonly indexed, and an index may also be created for another frequently searched attribute. A key defines identification or relationships; an index is primarily a performance structure. The terms are related but not interchangeable.
Interactive: Keys, Relationships and Index Lab
Use the three modes to test integrity constraints, compare relationship types and watch a simplified indexed lookup.
Common Mistakes and Misconceptions
- Choosing a key that is unique only in the sample data rather than guaranteed to be unique.
- Forgetting that a compound key is judged by the combined values.
- Assuming a foreign key must be unique.
- Placing the foreign key at the one side of a one-to-many relationship.
- Trying to store several foreign-key values as a list in one cell for a many-to-many relationship.
- Confusing referential integrity with validation of factual accuracy.
- Describing an index as another primary key rather than a lookup structure.
- Confusing a relation with a relationship.
Practice
Try these original questions
- State two conditions that an attribute must satisfy to be a candidate key.
- Explain the difference between a candidate key and a secondary key.
- Explain why CustomerName may be unsuitable as the primary key of Customer.
- A table records one attendance mark for each student on each date. Suggest a compound primary key and justify it.
- In Department(DepartmentID, DepartmentName) and Employee(EmployeeID, Name, DepartmentID), identify the primary and foreign keys.
- Explain why DepartmentID can repeat in Employee without violating key rules.
- Describe one example each of a 1:1, 1:M and M:M relationship.
- Explain how a link table resolves a many-to-many relationship.
- Describe what the DBMS should do if an employee is assigned a DepartmentID that does not exist.
- Explain one benefit and one cost of adding an index to a frequently searched field.
Review
| Term | Core meaning | Integrity or performance role |
|---|---|---|
| Primary key | The selected unique identifier for each tuple | Prevents duplicate or unidentified tuples |
| Candidate key | Any key that could serve as the primary key | Provides alternative valid identifiers |
| Secondary key | A candidate key not selected as primary | May still be used for searching or alternative identification |
| Compound key | A key made from several attributes | Identifies tuples when one attribute is insufficient |
| Foreign key | An attribute that references another table's primary key | Implements a relationship between tables |
| Referential integrity | Foreign-key references must point to existing tuples | Prevents broken or orphan references |
| Index | An additional structure mapping search values to tuples | Speeds retrieval at a storage and maintenance cost |