Skip to main content

Tell me about a 1:1 (one-to-one) relationship

SQLJunior~1 min read
Suggest an edit

A one-to-one (1:1) relationship is a type of table relationship where each record in one table corresponds to at most one record in another.

This kind of relationship is created rarely, but it's useful when you need to split one entity's data into two logical blocks.

Example

Say we have a users table and a passports table:

sql
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE passports ( id INT PRIMARY KEY, user_id INT UNIQUE, passport_number VARCHAR(20), FOREIGN KEY (user_id) REFERENCES users(id) );

Here:

  • users.id is the primary key of the users table,
  • passports.user_id is a foreign key that references users.id and has a UNIQUE constraint.

Thanks to UNIQUE, one user can have only one passport, and one passport has only one owner.

How it works

users tablepassports table
id = 1, name = 'Anna'user_id = 1, passport_number = 'AB123456'
id = 2, name = 'Ivan'user_id = 2, passport_number = 'CD987654'

Each users row can be linked to only one passports row.

When 1:1 is used

  1. To separate private data. For example, personal data (passport, tax ID) gets moved into a separate table with restricted access.
  2. For optimization. To avoid too many fields in one table, splitting by meaning (e.g. users and user_profiles).
  3. For rare extensions. When extra information applies to only some records. For example, if only some employees have a company car, the cars table would relate to employees as 1:1.

Example query

sql
SELECT users.name, passports.passport_number FROM users JOIN passports ON users.id = passports.user_id;

We get the user's name and their passport, one-to-one.

Summary: A 1:1 relationship is a strict pair, "one record <-> one record." It guarantees a unique relation between tables, and is used for separation, security, and structuring data.

Short Answer

Interview ready
Premium

A concise answer to help you respond confidently on this topic during an interview.

Suggest an edit