How a Foreign Key Works to Establish a Relationship
A primary key uniquely identifies each record in the table. It is a type of candidate key that is usually the first column in a table and can be automatically generated by the database to ensure that it is unique. A foreign key is another candidate key (not the primary key) used to link a record to data in another table. For example, consider these two tables that identify which teacher teaches which course. Here, the Courses table’s primary key is Course_ID. Its foreign key is Teacher_ID:
Types of Database Relationships
Using foreign keys, or other candidate keys, you can implement three types of relationships between tables:
One-to-One
This type of relationship allows only one record on each side of the relationship. The primary key relates to only one record (or none) in another table. For example, in a marriage, each spouse has only one other spouse. This kind of relationship can be implemented in a single table and therefore does not use a foreign key.
One-to-Many
A one-to-many relationship allows a single record in one table to be related to multiple records in another table. Consider a business with a database that has Customers and Orders tables. A single customer can purchase multiple orders, but a single order could not be linked to multiple customers. Therefore the Orders table would contain a foreign key that matched the primary key of the Customers table, while the Customers table would have no foreign key pointing to the Orders table.
Many-to-Many
This is a complex relationship in which many records in a table can link to many records in another table. For example, our business probably needs Customers and Orders tables, and likely also needs a Products table. Again, the relationship between the Customers and Orders table is one-to-many, but consider the relationship between the Orders and Products table. An order can contain multiple products, and a product could be linked to multiple orders since several customers might submit an order that contains some of the same products. This kind of relationship requires three tables at a minimum.
Why Are Database Relationships Important?
Establishing consistent relationships between database tables helps ensure data integrity, contributing to database normalization. For example, what if we did not link any tables through a foreign key and instead combined the data in the Courses and Teachers tables, like so: Or perhaps we decided to add a second record for Carmen, in order to enforce 1NF: Breaking this table into two tables, Teachers and Courses, creates the proper relationship between the data and therefore helps ensure data consistency and accuracy.
