MySQL One to One: When and How to Split a Table

In a one-to-one relationship, each row of table A matches at most one row of table B, and the other way round. It is the rarest relationship, and often it means the two tables could be one.

Pattern 1: a unique foreign key

query.sql

SQL

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL
);

CREATE TABLE user_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    display_name VARCHAR(60) NOT NULL,
    bio TEXT NULL,
    CONSTRAINT uq_profile_user UNIQUE (user_id),
    CONSTRAINT fk_profile_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

INSERT INTO users (email, password_hash) VALUES ('asha@example.com', 'x'), ('ben@example.com', 'y');
INSERT INTO user_profiles (user_id, display_name) VALUES (1, 'Asha R.');

The UNIQUE on user_id is what makes it one-to-one: two profiles cannot point to the same user.

query.sql

SQL

INSERT INTO user_profiles (user_id, display_name) VALUES (1, 'Asha again');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry '1' for key 'user_profiles.uq_profile_user'

Pattern 2: a shared primary key

The child’s primary key is also the foreign key. The row can only exist once per parent, by definition:

query.sql

SQL

CREATE TABLE user_settings (
    user_id INT UNSIGNED PRIMARY KEY,
    theme VARCHAR(10) NOT NULL DEFAULT 'light',
    newsletter BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT fk_settings_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);
INSERT INTO user_settings (user_id) VALUES (1);

This is a very clean form, with one less column and one less index.

Querying

query.sql

SQL

SELECT u.email, p.display_name, s.theme
FROM users u
LEFT JOIN user_profiles p ON p.user_id = u.id
LEFT JOIN user_settings s ON s.user_id = u.id
ORDER BY u.id;

Output

OUTPUT

+------------------+--------------+-------+
| email            | display_name | theme |
+------------------+--------------+-------+
| asha@example.com | Asha R.      | light |
| ben@example.com  | NULL         | NULL  |
+------------------+--------------+-------+
2 rows in set

Ben has no profile or settings yet, and LEFT JOIN keeps him. A one-to-one child is often optional: use LEFT JOIN to read it.

Why split a table at all?

Often you should not. If every user has exactly one profile, you could put display_name and bio into users. Split in these cases:

Rarely used, large columns. A TEXT biography, a big JSON blob or a BLOB that you do not need on every SELECT. Moving it out keeps the main table narrow and the common queries fast.
Different access rights. Sensitive data (tax numbers, health data) in a separate table that only some accounts may read.
Different lifecycle. The extra data is created later, or deleted separately.
Optional groups of columns. A drivers table where only some employees are drivers. Their driving-licence columns would be NULL for everyone else.
A special case of a general entity (subtype). vehicles with cars and trucks, each with its own extra columns. The subtype table shares the primary key of the parent.

query.sql

SQL

CREATE TABLE vehicles (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, plate VARCHAR(15) NOT NULL UNIQUE, type VARCHAR(10) NOT NULL);
CREATE TABLE trucks (
    vehicle_id INT UNSIGNED PRIMARY KEY,
    load_capacity_kg INT UNSIGNED NOT NULL,
    CONSTRAINT fk_truck_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles (id) ON DELETE CASCADE
);
INSERT INTO vehicles (plate, type) VALUES ('MH12AB1234', 'truck'), ('MH14CD5678', 'car');
INSERT INTO trucks VALUES (1, 12000);

SELECT v.plate, v.type, t.load_capacity_kg FROM vehicles v LEFT JOIN trucks t ON t.vehicle_id = v.id ORDER BY v.id;

Output

OUTPUT

+------------+-------+------------------+
| plate      | type  | load_capacity_kg |
+------------+-------+------------------+
| MH12AB1234 | truck |            12000 |
| MH14CD5678 | car   |             NULL |
+------------+-------+------------------+
2 rows in set

When to keep one table

If the data is small, always loaded together, and has the same lifecycle, one table is simpler. A join costs something, and you must keep two rows in step. Splitting “just in case” is over-engineering.

Enforcing “exactly one”

SQL cannot easily say “every user must have a profile” (a required one-to-one). The database cannot insert two rows at once atomically with a foreign key check in both directions. Usually the application creates both rows in one transaction, and the child row’s presence is optional in the schema.

info

Check the cardinality

Many “one-to-one” relationships turn out to be one-to-many a year later (a user gets several profiles, a person several phone numbers). The UNIQUE constraint can be dropped later. Think about whether your data really can only ever be one.