Back to articles list
What is a one-to-one relationship in data modeling? How do you implement this relationship in a database? The examples in this article will answer these questions.
There are three types of relationships between entities (tables) in data modeling:
- One-to-many relationships (also denoted as 1:M).
- Many-to-many relationships (M:N).
- One-to-one relationships (1:1).
The most common type of relationship is a one-to-many relationship, where a record in one entity can be referenced by multiple records in another entity. Another common type is a many-to-many relationship. This type of relationship is only used in logical data models. In a physical database, it has to be implemented by using one-to-many relationships and a junction table.
In this article, we’ll discuss the third type of relationships: the one-to-one relationship. This is the least common type of relationship in a data model. We’ll give examples of one-to-one relationships, show the notation for one-to-one relationships in an ER diagram, and discuss one-to-one relationships in practice.
Examples of One-to-One Relationships
First, what is a one-to-one relationship? It’s a relationship where a record in one entity (table) is associated with exactly one record in another entity (table).
Let’s see some real-life examples of one-to-one relationships:
- Country - capital city: Each country has exactly one capital city. Each capital city is the capital of exactly one country.
- Person - their fingerprints. Each person has a unique set of fingerprints. Each set of fingerprints identifies exactly one person.
- Email - user account. For many websites, one email address is associated with exactly one user account and each user account is identified by its email address.
- Spouse - spouse: In a monogamous marriage, each person has exactly one spouse.
- User profile - user settings. One user has one set of user settings. One set of user settings is associated with exactly one user.
For clarity, let’s contrast these examples with relationships that are not one-to-one:
- Country - city: Each city is in exactly one country, but most countries have many cities.
- Parent - child: Each child has two parents, but each parent can have many children.
- Employee - manager: Each employee has exactly one immediate supervisor or manager, but each manager usually supervises many employees.
Denoting a One-to-One Relationship in an ER Diagram
A one-to-one relationship in an ER diagram is denoted, like all relationships, with a line connecting the two entities. The “one” cardinality is denoted with a single straight line. (The “many” cardinality is denoted with a crow’s foot symbol .)
The one-to-one relationship between country and capital can be denoted like this:
The perpendicular straight lines mean “mandatory”. This diagram shows that it’s mandatory for a capital to have a country and it’s mandatory for a country to have a capital.
Another possibility is for one or both of the sides of the relationship to be optional. An optional side is denoted with an open circle. This diagram says that there is a one-to-one relationship between a person and their fingerprints. A person is mandatory (fingerprints must be assigned to a person), but fingerprints are optional (a person may have no fingerprints assigned in the database).
One-to-One Relationships in a Physical Database
There are a few ways to implement a one-to-one relationship in a physical database.
Primary Key as Foreign Key
One way to implement a one-to-one relationship in a database is to use the same primary key in both tables. Rows with the same value in the primary key are related. In this example, France is a country with the id 1 and its capital city is in the table capital under id 1.
country
| id | name |
|---|---|
| 1 | France |
| 2 | Germany |
| 3 | Spain |
capital
Technically, one of the primary keys has to be marked as foreign key, like in this data model:
The primary key in table capital is also a foreign key which references the id column in the table country. Since capital.id is a primary key, each value in the column is unique, so the capital can reference at most one country. It also must reference a country – it’s a primary key, so it cannot be left empty.
Additional Foreign Key with Unique Constraint
Another way you can implement a one-to-one relationship in a database is to add a new column and make it a foreign key.
In this example, we add the column country_id in the table capital. The capital with id 1, Madrid, is associated with country 3, Spain.
country
| id | name |
|---|---|
| 1 | France |
| 2 | Germany |
| 3 | Spain |
capital
| id | name | country_id |
|---|---|---|
| 1 | Madrid | 3 |
| 2 | Berlin | 2 |
| 3 | Paris | 1 |
Technically, the column country_id should be a foreign key referencing the id column in the table country. Since you want each capital to be associated with exactly one country, you should make the foreign key column country_id unique.
One-to-One Relationships in Practice
Few One-to-One Relationships Last
One-to-one relationships are the least frequent relationship type. One of the reasons for this is that very few one-to-one relationships exist in real life. Also, most one-to-one relationships are one-to-one only for some period of time. If your model includes a time component and captures change history, as is very often the case, you’ll have very few one-to-one relationships.
A monogamous relationship may split up or one of the partners may die. If you model the reality of monogamous relationships (such as marriages or civil unions) over time, you’ll likely need to model the fact that they last only for a certain period.
You’d think that a person and their fingerprints never change. But what if the person loses a finger or the finger is badly burnt? Their fingerprints might change. It’s not a very frequent scenario; still, in some models, you may need to take this into account.
Even something seemingly as stable as countries and their capitals change over time. For example, Bonn used to be the capital of West Germany (Bundesrepublik Deutschland) after World War II, when Berlin was part of East Germany. This changed after German reunification; the capital of Germany (Bundesrepublik Deutschland) is now Berlin. Whether you should or should not take this into account depends on your business reality and the application you’re working on.
A Feasible 1:1 Scenario: Optional Parts of Table
I can think of one feasible scenario for a real one-to-one relationship: optional parts of a table. Imagine you have the table user with user data. The table contains general user information, such as users’ names, email addresses, and signup dates. It also contains user settings, such as the color theme or auto-login for that app. However, most users don’t have any user settings; they use the default settings.
user
| id | name | signup_ date | theme | autologin | |
|---|---|---|---|---|---|
| 1 | Nathanael Talbot | nat@example.com | 2020-12-12 | dark | true |
| 2 | Talitha Yates | yates@example.com | 2020-12-14 | ||
| 3 | Markus Weir | weir@example.com | 2020-12-15 | light | false |
| 4 | Nathalie Hays | hays@example.com | 2020-12-18 | ||
| 5 | Maurice Church | mch@example.com | 2020-12-20 | ||
| 6 | Arwa Valdez | arval@example.com | 2020-12-21 |
There are a lot of empty fields in this table. You could split the user table into two tables: user and user_settings, which contains information about users settings for those who chose to select them.
user
| id | name | signup_ date | theme | autologin | |
|---|---|---|---|---|---|
| 1 | Nathanael Talbot | nat@example.com | 2020-12-12 | dark | true |
| 2 | Talitha Yates | yates@example.com | 2020-12-14 | ||
| 3 | Markus Weir | weir@example.com | 2020-12-15 | light | false |
| 4 | Nathalie Hays | hays@example.com | 2020-12-18 | ||
| 5 | Maurice Church | mch@example.com | 2020-12-20 | ||
| 6 | Arwa Valdez | arval@example.com | 2020-12-21 |
user_settings
| user_id | theme | autologin |
|---|---|---|
| 1 | dark | true |
| 3 | light | false |
Splitting data into two tables makes table querying more complex: you have to join data from both tables. On the other hand, the main user table is simpler to manage.
Learn More About Database Relationships
A one-to-one relationship is a relationship where a record in one table is associated with exactly one record in another table. This type of relationship is rare in real life. Keep in mind that such logical questions are popular in entry-level SQL job interviews. If you include time in your data model, many one-to-one relationships become one-to-many or many-to-many relationships. The most common scenario for using a one-to-one relationship in a database is splitting one table into two: one with mandatory columns, the other with optional columns.
If you liked this article, check out other articles on one-to-many and many-to-many relationships on our blog.
If you’re a student taking database classes, make sure to create a free Academic account in Vertabelo, our online ER diagram drawing tool. Vertabelo allows you to draw logical and physical ER diagrams directly in your browser. It supports PostgreSQL, SQL Server, Oracle, MySQL, Google BigQuery, Amazon Redshift, and other relational databases. Try it out and see how easy it is to get started!
