One-to-one relationship design in SQL

Introduction

A one-to-one relationship links two pieces of information so each item in one table matches exactly one item in another table. This keeps data organized and avoids repeating details.

When you want to store extra details about a person separately, like a user and their profile.

When you want to split sensitive information into a different table for security.

When you want to keep optional information separate to save space.

When two tables share the same primary key but hold different types of data.

When you want to clearly separate data for easier updates and maintenance.

Syntax

SQL

CREATE TABLE TableA (
  id INT PRIMARY KEY,
  columnA datatype
);

CREATE TABLE TableB (
  id INT PRIMARY KEY,
  columnB datatype,
  FOREIGN KEY (id) REFERENCES TableA(id)
);

The primary key in TableB is also a foreign key referencing TableA's primary key.

This setup ensures each row in TableB matches exactly one row in TableA.

Examples

Each person has one passport. The passport's person_id matches the person's person_id.

SQL

CREATE TABLE Person (
  person_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE Passport (
  person_id INT PRIMARY KEY,
  passport_number VARCHAR(20),
  FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

EmployeeDetails stores extra info for each employee, linked by employee_id.

SQL

CREATE TABLE Employee (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE EmployeeDetails (
  employee_id INT PRIMARY KEY,
  address VARCHAR(200),
  phone VARCHAR(15),
  FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
);

Sample Program

This example creates two tables linked one-to-one by user_id. It inserts one user and their profile, then selects both together.

SQL

CREATE TABLE User (
  user_id INT PRIMARY KEY,
  username VARCHAR(50)
);

CREATE TABLE UserProfile (
  user_id INT PRIMARY KEY,
  bio TEXT,
  FOREIGN KEY (user_id) REFERENCES User(user_id)
);

INSERT INTO User (user_id, username) VALUES (1, 'alice');
INSERT INTO UserProfile (user_id, bio) VALUES (1, 'Loves hiking and reading.');

SELECT u.user_id, u.username, p.bio
FROM User u
JOIN UserProfile p ON u.user_id = p.user_id;

Important Notes

Make sure the foreign key column in the second table is also the primary key to enforce one-to-one.

You can also use UNIQUE constraints on the foreign key if the primary key is different.

One-to-one relationships help keep data clean and avoid duplication.

Summary

One-to-one relationships link exactly one row in one table to one row in another.

Use the same primary key in both tables or a unique foreign key to enforce this.

This design helps organize data and separate concerns.