One-to-One (1:1) Relationships0%

One-to-One (1:1) Relationships

Intermediate12 min read•Updated: 2026-09-12

One-to-One (1:1) Relationships & Foreign Key Placement

In relational database engineering, relationships define how records in one table correlate with records in another. A One-to-One (1:1) relationship means that a single record in Table A is associated with at most one record in Table B, and vice versa.

While beginners often ask: "Why not just put all columns in a single table?", experienced database architects know that 1:1 relationships are critical for security isolation, performance optimization, and vertical partitioning.

1. Architectural Justifications for 1:1 Tables

Why split data into two tables linked in a 1:1 relationship?

1
Security & Sensitive Data Isolation: You can store public user profiles in users (readable by all services) and isolate sensitive fields (tax ID, KYC documents, banking details) in user_tax_details with strict table-level access permissions.
2
Performance & Vertical Partitioning: In MySQL InnoDB, reading narrow tables is significantly faster. Storing frequently read columns (username, email) in one table and rarely read wide columns (PDF resumes, JSON metadata) in a child table keeps your main table's pages compact in the Buffer Pool.
3
Sparse Columns: If only 2% of users have enterprise billing profiles, keeping those 15 billing columns in the main table would leave 98% of rows filled with empty NULL values.

2. Implementing a 1:1 Relationship in MySQL

A 1:1 relationship is enforced by placing a Foreign Key in the dependent table and applying a UNIQUE constraint on that foreign key column!

 PARENT TABLE: users
 +---------+-------------------+---------------------+
 | user_id | username          | email               |  <-- user_id is PRIMARY KEY
 +---------+-------------------+---------------------+
 |       1 | aarav_s           | aarav@example.com   |
 |       2 | diya_p            | diya@example.com    |
 +---------+-------------------+---------------------+
            ^
            | 1 : 1 (Enforced by UNIQUE Foreign Key!)
            |
 CHILD TABLE: user_passports
 +-------------+---------+-----------------+--------------+
 | passport_id | user_id | passport_number | expiry_date  |
 +-------------+---------+-----------------+--------------+
 |         101 |       1 | Z1234567        | 2032-05-10   |
 |         102 |       2 | J9876543        | 2030-11-20   |
 +-------------+---------+-----------------+--------------+

DDL Implementation:

1-- Parent Table: General User Identity
2CREATE TABLE users (
  user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
7) ENGINE = InnoDB;
8 
9-- Child Table: Passport / Identity Card
10CREATE TABLE user_passports (
  passport_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  passport_number VARCHAR(20) NOT NULL UNIQUE,
  issuing_country CHAR(2) NOT NULL DEFAULT 'IN',
  expiry_date DATE NOT NULL,
  
  -- Crucial: UNIQUE constraint turns a 1:N foreign key into a 1:1 relationship!
  CONSTRAINT uq_user_passport UNIQUE (user_id),
  
  CONSTRAINT fk_passport_user
      FOREIGN KEY (user_id) 
      REFERENCES users(user_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
25) ENGINE = InnoDB;

3. Alternative: Shared Primary Key Architecture

Instead of generating a separate surrogate key (passport_id), the child table can use the exact same Primary Key as the parent table, doubling as both Primary Key and Foreign Key:

1CREATE TABLE user_preferences (
  user_id INT UNSIGNED PRIMARY KEY, -- Serves as PK AND FK!
  theme ENUM('Light', 'Dark', 'System') NOT NULL DEFAULT 'System',
  email_notifications BOOLEAN NOT NULL DEFAULT TRUE,
  
  CONSTRAINT fk_pref_user 
      FOREIGN KEY (user_id) REFERENCES users(user_id) 
      ON DELETE CASCADE
9) ENGINE = InnoDB;

4. Querying 1:1 Relationships with Joins

1-- Retrieve complete user profile with passport info:
2SELECT 
3    u.user_id,
4    u.username,
5    u.email,
6    p.passport_number,
7    p.expiry_date
8FROM users u
9LEFT JOIN user_passports p ON u.user_id = p.user_id;

5. Best Practices & Common Pitfalls

Do Not Forget the UNIQUE Constraint: If you add a foreign key without a UNIQUE constraint, the database engine will allow multiple child rows per parent, inadvertently creating a One-to-Many (1:N) relationship!
Cascading Deletions: Always declare ON DELETE CASCADE on 1:1 auxiliary detail tables so that deleting a user automatically cleans up their associated preferences and passport records.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What database constraint must be added to a Foreign Key column to enforce a strict One-to-One (1:1) relationship?

Question 2

What is a primary architectural justification for splitting customer data into a 1:1 relationship rather than storing everything in one table?

Question 3

In a Shared Primary Key 1:1 architecture, what role does the child table's primary key play?

Question 4

Which referential action should generally be attached to a 1:1 dependent profile table when the parent user is deleted?

Question 5

What type of SQL join should be used to retrieve all users along with their 1:1 passport details, even if some users have not yet submitted a passport?

Next Lesson

One-to-Many (1:N) Relationships

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
None	One-to-Many (1:N) Relationships

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

One-to-Many (1:N) Relationships

One-to-One (1:1) Relationships

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

One-to-One (1:1) Relationships & Foreign Key Placement

1. Architectural Justifications for 1:1 Tables

Why split data into two tables linked in a 1:1 relationship?

1
Security & Sensitive Data Isolation: You can store public user profiles in users (readable by all services) and isolate sensitive fields (tax ID, KYC documents, banking details) in user_tax_details with strict table-level access permissions.
2
Performance & Vertical Partitioning: In MySQL InnoDB, reading narrow tables is significantly faster. Storing frequently read columns (username, email) in one table and rarely read wide columns (PDF resumes, JSON metadata) in a child table keeps your main table's pages compact in the Buffer Pool.
3
Sparse Columns: If only 2% of users have enterprise billing profiles, keeping those 15 billing columns in the main table would leave 98% of rows filled with empty NULL values.

2. Implementing a 1:1 Relationship in MySQL

A 1:1 relationship is enforced by placing a Foreign Key in the dependent table and applying a UNIQUE constraint on that foreign key column!

 PARENT TABLE: users
 +---------+-------------------+---------------------+
 | user_id | username          | email               |  <-- user_id is PRIMARY KEY
 +---------+-------------------+---------------------+
 |       1 | aarav_s           | aarav@example.com   |
 |       2 | diya_p            | diya@example.com    |
 +---------+-------------------+---------------------+
            ^
            | 1 : 1 (Enforced by UNIQUE Foreign Key!)
            |
 CHILD TABLE: user_passports
 +-------------+---------+-----------------+--------------+
 | passport_id | user_id | passport_number | expiry_date  |
 +-------------+---------+-----------------+--------------+
 |         101 |       1 | Z1234567        | 2032-05-10   |
 |         102 |       2 | J9876543        | 2030-11-20   |
 +-------------+---------+-----------------+--------------+

DDL Implementation:

1-- Parent Table: General User Identity
2CREATE TABLE users (
  user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
7) ENGINE = InnoDB;
8 
9-- Child Table: Passport / Identity Card
10CREATE TABLE user_passports (
  passport_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  passport_number VARCHAR(20) NOT NULL UNIQUE,
  issuing_country CHAR(2) NOT NULL DEFAULT 'IN',
  expiry_date DATE NOT NULL,
  
  -- Crucial: UNIQUE constraint turns a 1:N foreign key into a 1:1 relationship!
  CONSTRAINT uq_user_passport UNIQUE (user_id),
  
  CONSTRAINT fk_passport_user
      FOREIGN KEY (user_id) 
      REFERENCES users(user_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
25) ENGINE = InnoDB;

3. Alternative: Shared Primary Key Architecture

Instead of generating a separate surrogate key (passport_id), the child table can use the exact same Primary Key as the parent table, doubling as both Primary Key and Foreign Key:

1CREATE TABLE user_preferences (
  user_id INT UNSIGNED PRIMARY KEY, -- Serves as PK AND FK!
  theme ENUM('Light', 'Dark', 'System') NOT NULL DEFAULT 'System',
  email_notifications BOOLEAN NOT NULL DEFAULT TRUE,
  
  CONSTRAINT fk_pref_user 
      FOREIGN KEY (user_id) REFERENCES users(user_id) 
      ON DELETE CASCADE
9) ENGINE = InnoDB;

4. Querying 1:1 Relationships with Joins

1-- Retrieve complete user profile with passport info:
2SELECT 
3    u.user_id,
4    u.username,
5    u.email,
6    p.passport_number,
7    p.expiry_date
8FROM users u
9LEFT JOIN user_passports p ON u.user_id = p.user_id;

5. Best Practices & Common Pitfalls

Do Not Forget the UNIQUE Constraint: If you add a foreign key without a UNIQUE constraint, the database engine will allow multiple child rows per parent, inadvertently creating a One-to-Many (1:N) relationship!
Cascading Deletions: Always declare ON DELETE CASCADE on 1:1 auxiliary detail tables so that deleting a user automatically cleans up their associated preferences and passport records.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What database constraint must be added to a Foreign Key column to enforce a strict One-to-One (1:1) relationship?

Question 2

What is a primary architectural justification for splitting customer data into a 1:1 relationship rather than storing everything in one table?

Question 3

In a Shared Primary Key 1:1 architecture, what role does the child table's primary key play?

Question 4

Which referential action should generally be attached to a 1:1 dependent profile table when the parent user is deleted?

Question 5

What type of SQL join should be used to retrieve all users along with their 1:1 passport details, even if some users have not yet submitted a passport?

Next Lesson

One-to-Many (1:N) Relationships

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
None	One-to-Many (1:N) Relationships

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.