One-to-One Relationship

Introduction

A one-to-one relationship in a database means that one record in one entity is associated with at most one corresponding record in another entity, and the same is true in the opposite direction according to the business rule. In simple words, one user can have one profile, and one profile belongs to one user. One employee can have one private-details record, and one private-details record belongs to one employee. One account can have one preference record, and one preference record belongs to one account.

One-to-one relationships look simple, but they are often misunderstood. A foreign key alone does not automatically create a one-to-one relationship. A foreign key creates a reference. To make the relationship one-to-one, the referencing value must also be unique, or the child table must use the parent's key as its own primary key. This distinction is one of the most important interview and real-world design points.

USER
  1
  |
  0..1
USER_PROFILE

Common meaning:
One user can have zero or one profile.
Every profile belongs to one user.

In relational databases, one-to-one relationships are usually implemented with a primary key, a foreign key, and a uniqueness rule. Another clean approach is the shared primary key pattern, where the child table uses the same key value as both its primary key and foreign key. Both patterns can be correct, but the right choice depends on the business meaning of the two entities.

Basic One-to-One Concept

Consider two entities: User and User Profile. The business rule says each user can have only one profile. User 101 may have profile 1001. User 102 may have profile 1002. User 103 may have profile 1003. What should not happen is user 101 having profile 1001 and profile 1002 at the same time, because that would make the relationship one-to-many.

Allowed:
User 101 -> Profile 1001
User 102 -> Profile 1002

Not allowed in 1:1:
User 101 -> Profile 1001
User 101 -> Profile 1002

The relationship type is defined by the business rule, not by the table names. If a company says an employee can have many badges over time, Employee to Badge is one-to-many. If the system tracks exactly one active badge record per employee, Employee to Active Badge may be one-to-one. The same real-world words can produce different database relationships depending on the requirement.

This is why database modeling always starts with precise business rules. A designer should not assume one-to-one because two tables sound related. The designer must ask how many records are allowed on each side, whether the relationship is required or optional, and whether either side has an independent lifecycle.

Real-World One-to-One Examples

Common examples of one-to-one relationships include User and User Profile, Employee and Employee Private Details, Person and Passport in a simplified passport system, Vehicle and Vehicle Detail Record, Customer and Customer Preference Record, Account and Account Settings, Product and Product SEO Metadata, and User Account and User Credential. Each example is only one-to-one if the business rules say so.

User and User Profile is often optional. A user can register and use the system before completing a profile. In that case, a user may have zero or one profile, while every profile must belong to one user. Employee and Employee Private Details may be mandatory in some payroll systems, but optional in others depending on when private details are collected.

Product and Product SEO Metadata is a practical website example. The products table may contain core product information such as product_id, name, price, and stock. The product_seo table may contain meta_title, meta_description, canonical_url, and social sharing metadata. If each product can have at most one SEO metadata row, that is one-to-one.

Foreign Key Alone Does Not Guarantee One-to-One

A common beginner mistake is thinking that a foreign key automatically creates one-to-one. It does not. A foreign key only ensures that a referenced parent row exists. If the foreign key column is not unique, many child rows can reference the same parent row. That is one-to-many.

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT,
    bio VARCHAR(500),
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

The table above does not fully enforce one-to-one. It allows profile_id 1001, 1002, and 1003 to all contain user_id 101 unless another constraint prevents it. The foreign key guarantees that user 101 exists, but it does not stop many profiles from pointing to user 101.

This data violates the intended one-to-one rule. To prevent it, the child foreign key must be unique, or the child table must use the parent key as its own primary key.

Unique Foreign Key Pattern

The unique foreign key pattern uses a separate primary key in the child table and a foreign key column that is also unique. This lets the child have its own identifier while still enforcing maximum one child per parent.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(100) NOT NULL
);

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    bio VARCHAR(500),
    website VARCHAR(200),
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

The UNIQUE constraint on user_profiles.user_id is the critical part. It ensures that user_id cannot repeat in the profile table. If user 101 already has a profile row, inserting another profile row with user_id 101 fails. The foreign key ensures the user exists. The unique constraint ensures there is at most one profile for that user.

Primary Key   -> identifies each profile row
Foreign Key   -> connects profile to user
UNIQUE(user_id) -> prevents multiple profiles for same user

This pattern is useful when the child entity has its own meaningful identifier or when the system already uses a separate child ID. For example, profile_id may be used by another service, external import, audit trail, or legacy system.

Shared Primary Key Pattern

The shared primary key pattern is often cleaner when the child record exists mainly as an extension of the parent record. In this pattern, the child table's key is the same as the parent key. The child column is both primary key and foreign key.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(100) NOT NULL
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio VARCHAR(500),
    website VARCHAR(200),
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

Here, user_profiles.user_id cannot repeat because it is the primary key of user_profiles. It also cannot reference a nonexistent user because it is a foreign key to users.user_id. This naturally enforces maximum one profile per user and ensures every profile belongs to an existing user.

USERS
-----
user_id PK
username
    |
    |
USER_PROFILES
-------------
user_id PK + FK
bio
website

The shared primary key pattern is common when the child table is strongly dependent on the parent. Employee private details, user profile extensions, product SEO metadata, and account preference records often fit this pattern when the child has no independent business identity separate from the parent.

Optionality in One-to-One Relationships

The phrase one-to-one is often used casually, but precise modeling must include optionality. A user may have zero or one profile. A profile must belong to exactly one user. That is not exactly the same as saying every user must have a profile. The correct notation is often User 1 to 0..1 User Profile.

A shared primary key schema ensures that a profile cannot exist without a user and that a user cannot have more than one profile. However, it still allows a user to exist with no profile row. Therefore, the structure naturally represents an optional child unless additional rules force every parent to have a child.

User 101 -> Profile 101
User 102 -> no profile
User 103 -> Profile 103

This is valid for:
USER 1 -> 0..1 USER_PROFILE

Enforcing exactly one on both sides is harder. A foreign key from profile to user easily enforces that every profile has a user. It does not automatically enforce that every user has a profile. That may require transactional creation logic, application rules, deferred constraints, database triggers, or a design decision to keep the data in one table instead.

Mandatory One-to-One Relationships

A mandatory one-to-one relationship means every record on one side must have a matching record on the other side. For example, a system may require every employee to have exactly one employee detail record. Conceptually this is Employee 1 to 1 Employee Detail.

However, if every employee must always have detail data, the first question should be whether two tables are really necessary. If the details have no separate lifecycle, no separate permissions, no storage reason, and no modular boundary, one table may be simpler and more appropriate.

Mandatory one-to-one splits are usually justified by stronger reasons such as security, privacy, separate access patterns, legacy extension constraints, or modular ownership. For example, a general employee table may be visible to HR and managers, while employee_private may be restricted to payroll services.

Why Split One Entity Across Two Tables?

At first glance, one-to-one relationships can look unnecessary. If there is one row on each side, why not keep all columns in one table? That is the right question. A one-to-one split should normally have a clear reason. Otherwise, it creates extra tables, extra joins, extra constraints, extra migrations, and more application mapping.

One reason to split is security. General employee data such as name, department, and email may be stored in employees, while sensitive data such as tax identifier, bank account, salary details, or background check status may be stored in employee_private. Different database permissions, application services, and audit rules can then apply to the sensitive table.

A second reason is optional data. If only some users create detailed profiles, putting bio, website, LinkedIn URL, GitHub URL, profile image, and preferences inside users may create many NULL values. A separate user_profiles table stores profile data only for users who provide it.

A third reason is large or infrequently used data. If a table has a few columns that are rarely needed but large, splitting them can sometimes improve access patterns. This should not be done blindly; actual workload and database behavior should guide the decision.

Other reasons include different lifecycle, modular responsibility, legacy extension, and separate ownership. User account information may be owned by authentication logic, while profile information may be owned by profile management logic. The one-to-one relationship can reflect those boundaries.

When Not to Use One-to-One

Do not split tables simply because you can. If every customer has exactly one email and the email has no separate lifecycle, security rule, or storage reason, creating CUSTOMER and CUSTOMER_EMAIL as separate one-to-one tables may add unnecessary complexity. A simpler customer table with an email column may be better.

Usually unnecessary:
CUSTOMER(customer_id, first_name, last_name)
CUSTOMER_EMAIL(customer_id, email)

Often simpler:
CUSTOMER(customer_id, first_name, last_name, email)

Unnecessary one-to-one tables can make queries harder to read and slower to execute. They require joins for data that could have been retrieved from one table. They also increase the number of migrations, indexes, foreign keys, test cases, and data repair tasks.

A good rule is to use one-to-one tables only when the split communicates a real modeling or operational reason. Security, optionality, separate lifecycle, large rarely accessed data, module boundaries, and legacy extension are reasonable reasons. Splitting every small group of columns is not.

Insert Workflow

Because the child table references the parent table, the parent row usually must be inserted first. In a user-profile relationship, create the user first and then create the profile. The foreign key protects the relationship by preventing the profile from referencing a nonexistent user.

INSERT INTO users (
    user_id,
    username
)
VALUES (
    101,
    'john'
);

INSERT INTO user_profiles (
    user_id,
    bio
)
VALUES (
    101,
    'Java Developer'
);

If the application tries to insert a profile for user 999 before user 999 exists, the database should reject the insert when the foreign key is enforced. This catches bad data at the database level and prevents orphan profile records.

INSERT INTO user_profiles (
    user_id,
    bio
)
VALUES (
    999,
    'QA Engineer'
);

-- Fails if user 999 does not exist.

Duplicate Child Prevention

The one-to-one rule must prevent a second child row from referencing the same parent. In the shared primary key pattern, the primary key prevents duplicate user_id values in user_profiles. In the unique foreign key pattern, UNIQUE(user_id) prevents duplicates.

INSERT INTO user_profiles (user_id, bio)
VALUES (101, 'Java Developer');

INSERT INTO user_profiles (user_id, bio)
VALUES (101, 'Another Profile');

-- Fails because user_id already exists in user_profiles.

This test is valuable because it proves the database is enforcing the maximum-one rule. If the second insert succeeds, the relationship is not one-to-one from the database's perspective.

Querying One-to-One Data

One-to-one relationships are queried using joins just like other relationships. If every user you want to display must have a profile, an INNER JOIN can be used. If profile data is optional and you still want all users, a LEFT JOIN is usually appropriate.

SELECT
    u.user_id,
    u.username,
    p.bio,
    p.website
FROM users u
JOIN user_profiles p
    ON u.user_id = p.user_id;

The INNER JOIN returns only users who have matching profile rows. If Alice has no profile, she will not appear in the result. That is correct when the query is intentionally looking for users with profiles, but it is wrong if the application needs all users.

SELECT
    u.user_id,
    u.username,
    p.bio
FROM users u
LEFT JOIN user_profiles p
    ON u.user_id = p.user_id;

The LEFT JOIN returns all users and shows NULL for profile columns when no profile exists. This is the practical connection between relationship optionality and SQL query choice. Understanding the model helps you choose the correct join.

Employee Private Data Example

An employee system often separates general employee information from sensitive private information. The employees table may store employee_id, name, email, and department_id. The employee_private table may store tax_identifier, bank_account, and other restricted information. A shared primary key often fits because private details depend on the employee.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    department_id INT
);

CREATE TABLE employee_private (
    employee_id INT PRIMARY KEY,
    tax_identifier VARCHAR(50),
    bank_account VARCHAR(100),
    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
);

This structure can support separation of permissions and responsibilities. General employee queries do not need to touch employee_private. Payroll-related functionality can join the private table only when authorized. The relationship is still one-to-one because each private row belongs to one employee and each employee has at most one private row.

Customer Preferences Example

Customer preferences are often optional. A system may have default language, theme, and notification settings. Only customers who customize their preferences need a row in customer_preferences. This creates a natural one-to-zero-or-one relationship from Customer to Customer Preference.

CREATE TABLE customer_preferences (
    customer_id INT PRIMARY KEY,
    language VARCHAR(20),
    theme VARCHAR(20),
    notification_enabled BOOLEAN,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

If a customer has no preference row, the application can use default values. If the customer updates preferences later, a row is inserted. If the customer resets preferences to defaults, the row may be deleted depending on design. This optional lifecycle is a good reason for a one-to-one extension table.

Authentication Data Example

Applications sometimes separate user account data from authentication credentials. A users table may store user_id, name, email, and account status. A user_credentials table may store password_hash, password_changed_at, failed_login_count, or multi-factor configuration. The split can reflect security boundaries and modular ownership.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE user_credentials (
    user_id INT PRIMARY KEY,
    password_hash VARCHAR(255) NOT NULL,
    password_changed_at TIMESTAMP,
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

Table separation alone does not guarantee security. Proper permissions, encryption, hashing, audit logs, application authorization, and operational controls are still required. The one-to-one structure only gives a clean storage boundary.

Delete Behavior and Cascades

One-to-one relationships need clear delete behavior. If a user is deleted, what should happen to the profile? The answer depends on the business rules. The system may prevent user deletion, soft delete the user and profile, archive both records, delete the profile automatically, or retain certain records for compliance.

If the child record has no meaning without the parent, ON DELETE CASCADE may be appropriate. For example, if user_profiles only exists as an extension of users, deleting a user may delete the profile automatically. This should still be a deliberate decision.

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio VARCHAR(500),
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE
);

Do not use cascade automatically. If the child table contains legally required records or audit history, automatic deletion may violate business or compliance rules. Cascades are business decisions expressed through database constraints.

ON DELETE SET NULL is another possible strategy with a separate child primary key, but it only makes sense if the child can exist independently after the parent is removed. For a strongly dependent profile, a profile without a user may not make semantic sense.

NULL and Unique Caveats

If the child foreign key is nullable, the child row can exist without being connected to a parent. That may be valid in some designs, but it is not valid if every profile must belong to a user. In that case, use NOT NULL along with the foreign key.

user_id INT NOT NULL UNIQUE

UNIQUE behavior with NULL values can differ across database systems. Some systems allow multiple NULLs in a unique column, while others have different options or semantics. Do not design optional one-to-one relationships based on vague assumptions. Understand the behavior of the RDBMS you use and make optionality explicit.

One-to-One and Performance

A one-to-one split can sometimes improve performance when it separates large or rarely accessed columns from frequently accessed data. For example, a users table may be read constantly for login and authorization, while profile biography or large preference data may be read less often. Keeping the hot table smaller can sometimes help access patterns.

However, a one-to-one split can also hurt performance because queries that need both sets of data require joins. More tables also mean more indexes, constraints, and planning complexity. Performance should be measured rather than assumed. A theoretical benefit is not enough reason to split tables.

The unique foreign key or primary key used in a one-to-one relationship usually creates an index or equivalent structure to enforce uniqueness. This often supports lookups such as SELECT * FROM user_profiles WHERE user_id = 101. Exact implementation depends on the database engine.

One-to-One and Normalization

One-to-one relationships can appear during normalization or vertical decomposition. Vertical decomposition means splitting columns of one logical entity into separate tables. For example, User may be split into User Account and User Profile. The rows remain logically connected one-to-one.

Original:
USER(user_id, username, email, bio, website, avatar_url, preferences)

Split:
USER(user_id, username, email)
USER_PROFILE(user_id, bio, website, avatar_url, preferences)

Normalization does not mean splitting every group of columns into separate one-to-one tables. The decomposition should represent meaningful dependencies, security boundaries, optional data, lifecycle differences, or access patterns. Over-normalizing into unnecessary one-to-one tables can make the database harder to work with.

One-to-One Testing

Testing a one-to-one relationship should verify both the reference rule and the uniqueness rule. First, a valid parent-child insert should succeed. Second, a child referencing a nonexistent parent should fail. Third, a second child for the same parent should fail. Fourth, required foreign keys should reject NULL. Fifth, delete behavior should match the configured referential action.

-- Valid relationship
INSERT INTO users VALUES (101, 'john');
INSERT INTO user_profiles VALUES (101, 'Developer');

-- Missing parent
INSERT INTO user_profiles VALUES (999, 'Tester');
-- Expected: foreign key violation

-- Duplicate child
INSERT INTO user_profiles VALUES (101, 'Another Profile');
-- Expected: primary key or unique violation

Testing should also cover query behavior. If profile is optional, an INNER JOIN should return only users with profiles, while a LEFT JOIN should return all users. If the application expects all users, a wrong INNER JOIN can create missing-data defects.

Common Mistakes

The most common mistake is creating a foreign key without UNIQUE and calling it one-to-one. That schema actually permits one-to-many. The second mistake is assuming every one-to-one relationship means exactly one row must exist on both sides. Many one-to-one implementations represent one-to-zero-or-one in practice because the child record is optional.

Another mistake is splitting every entity into many one-to-one tables. CUSTOMER_NAME, CUSTOMER_EMAIL, CUSTOMER_PHONE, CUSTOMER_STATUS, and CUSTOMER_CREATED_DATE as separate one-to-one tables would usually be unnecessary and painful. Normal columns in a customer table are often better.

Circular foreign keys are another design smell. Creating USERS.profile_id referencing profiles and PROFILES.user_id referencing users can complicate inserts and deletes. Usually one well-chosen foreign key is enough. Wrong cascade behavior is also dangerous. ON DELETE CASCADE should not be applied to sensitive, audit, legal, or financial child records without clear approval.

Do not confuse unique data with a one-to-one relationship. A unique email column in users means one email value maps to at most one user row. That is not a relationship between two entities. A database relationship involves an association between tables or entity types.

Design Decision Questions

Before creating a one-to-one relationship, ask whether the two sides are truly separate entities. Why cannot the attributes remain in one table? Is one side optional? Does one side have a separate lifecycle? Does one side contain sensitive data? Is one side rarely accessed? Does one side require separate permissions? Which entity depends on the other? What should happen when the parent is deleted?

If the child has its own meaningful identity, a separate child primary key plus a unique foreign key may be appropriate. If the child exists entirely as an extension of the parent, a shared primary key is often cleaner. If there is no strong reason to separate the data, one table may be the better design.

Business Requirement
        |
Identify Two Entities
        |
Verify Maximum Cardinality = 1
        |
Determine Optionality
        |
Choose Dependent Entity
        |
Choose Implementation:
  - Unique Foreign Key
  - Shared Primary Key
        |
Choose Delete Behavior
        |
Create Constraints
        |
Test Relationship

Implementation Summary

The critical word is UNIQUE. Without uniqueness on the referencing side, a one-to-one relationship can silently become one-to-many. A foreign key creates a valid reference, while uniqueness controls maximum cardinality.

1:1 with separate child key:
PARENT(parent_id PK)
CHILD(child_id PK, parent_id FK UNIQUE)

1:1 with shared key:
PARENT(parent_id PK)
CHILD(parent_id PK + FK)

Complete SQL Example

The following example uses the shared primary key pattern. It models a user and an optional profile. Every profile belongs to one existing user. A user can have at most one profile. If a user is deleted, the profile is deleted automatically because the profile has no meaning without the user in this design.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio VARCHAR(500),
    website VARCHAR(200),
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE
);

INSERT INTO users
VALUES (
    101,
    'john',
    'john@example.com'
);

INSERT INTO user_profiles
VALUES (
    101,
    'Java Developer',
    'example.com'
);

The same user_id connects both records. Because user_id is the primary key in user_profiles, only one profile row can exist for user 101. Because it is also a foreign key, the profile cannot exist unless user 101 exists in users.

Interview-Ready Explanation

A short interview answer is: a one-to-one relationship means one row in one table is related to at most one row in another table. It is commonly implemented using a foreign key with a UNIQUE constraint or by using a shared primary key where the child table's primary key is also a foreign key to the parent table.

A stronger answer is: a foreign key alone does not enforce one-to-one because many child rows can reference the same parent. To enforce 1:1, the referencing column must be unique, or the child table must use the parent key as its own primary key. One-to-one relationships are useful for optional data, sensitive data, separate lifecycles, modular boundaries, rarely used large data, or legacy extension tables. Optionality must also be considered because many practical one-to-one designs are actually one-to-zero-or-one from the parent side.

Key Takeaway

A one-to-one relationship means that each record on one side can be associated with at most one record on the other side according to the business rule. The business rule determines whether the relationship is truly one-to-one. The database enforces that rule using primary keys, foreign keys, and uniqueness constraints.

Foreign Key Alone
        |
Valid reference, but not necessarily 1:1

Foreign Key + UNIQUE
        |
Maximum one child per parent

Shared Primary Key
        |
Child key is also parent reference

Use one-to-one relationships carefully. They are most useful when there is a real reason to split data, such as security, optional data, different lifecycle, separate permissions, large rarely used data, modular responsibility, or legacy extension. Do not split tables unnecessarily. Always document optionality, choose the correct implementation pattern, define delete behavior, and test that duplicate child records cannot be created.