A relationship works by matching data in key columns usually columns with the same name in both the tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table.
There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined.
Let us now look into the three types of relationships:
One-to-Many Relationships
A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.
For example, the Customers and Orders tables have a one-to-many relationship: each customer can place many orders, but each order comes from only one customer.
Many-to-Many Relationships
In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa.
You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.
For example, the Customers table and the Cookies table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the Orders table.
One-to-One Relationships
In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both the related columns are primary keys or have unique constraints.
This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:
There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined.
Let us now look into the three types of relationships:
One-to-Many Relationships
A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.
For example, the Customers and Orders tables have a one-to-many relationship: each customer can place many orders, but each order comes from only one customer.
Many-to-Many Relationships
In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa.
You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.
For example, the Customers table and the Cookies table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the Orders table.
One-to-One Relationships
In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both the related columns are primary keys or have unique constraints.
This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:
- Divide a table into many columns.
Isolate part of a table for security reasons.
Store data that is short-lived and could be easily deleted by simply deleting the table.
Store information that applies only to a subset of the main table.
0 Comments