One of the most powerful features of a relational database is the ability to link tables together. Instead of repeating information in every row, you store it once and reference it from other tables. This lesson explains how relationships and foreign keys work.
Why Tables Are Related
Imagine a community programs database. Each program is held at a location. You could store the location name and address in every row of the programs table — but if the location's address changes, you would need to update every program row. And if you mistype the address in one row, you now have inconsistent data.
The better approach: store location information once in a locations table, and reference it from the programs table using an identifier.
Foreign Keys
A foreign key is a column in one table that references the primary key of another table. In the example above, the programs table has a location_id column. That column is a foreign key — its values must match a location_id that exists in the locations table.
The database enforces this through referential integrity: you cannot insert a program that references a location that does not exist, and you cannot delete a location that programs still reference.
Types of Relationships
One-to-many is the most common relationship. One location can host many programs. One program can have many registrations. The "many" side holds the foreign key.
One-to-one relationships are less common. Each record in table A corresponds to exactly one record in table B. This is sometimes used to split a wide table into two for organizational or access-control reasons.
Many-to-many relationships require a third table. One participant can register for many programs, and one program can have many participants. You cannot represent this directly with a single foreign key. Instead, you create a junction table (also called a linking table or bridge table) — for example, a registrations table with a participant_id and a program_id. Each row in registrations represents one participant's registration in one program.
Parent and Child Records
In a relationship, the table being referenced is the parent and the table holding the foreign key is the child. A location is the parent; a program is the child. A program is the parent; a registration is the child.
You generally need to create the parent record before the child record, because the child's foreign key must reference something that already exists.
Avoiding Repeated Information
Relationships allow you to store each piece of information exactly once. If a location's address changes, you update one row in the locations table and every program that references that location automatically reflects the change. This is one of the core benefits of relational design.
A foreign key is a promise: "this value refers to a real record in another table." The database enforces that promise. If the referenced record does not exist, the database rejects the insert or update.
Key Takeaways
- Foreign keys link tables by referencing the primary key of another table.
- Referential integrity prevents orphaned records and invalid references.
- One-to-many is the most common relationship; the "many" side holds the foreign key.
- Many-to-many relationships require a junction table.
- Storing information once and referencing it reduces duplication and inconsistency.
- database foreign key explained
- one-to-many relationship database
- many-to-many junction table SQL
- referential integrity database