When should you split a table into a one-to-one relationship, and how do you enforce it?

A one-to-one relationship in SQL is a foreign key that is also unique. Either make the foreign key the child table's primary key (a shared primary key), or give the child its own id and put a UNIQUE constraint on the foreign key column. Put the key on the side that can be missing: a user doesn't have to be a host, so hosts.user_id references users.id, not the other way round. Split a table this way only when part of it has a different life from the rest. That means facts most rows don't have, columns written far more often than the others, data with stricter access rules, or a subtype. Otherwise keep the columns in one table.

That rule comes from the 100 schemas in the drawDB gallery, which contain 43 one-to-one references across 30 schemas. 16 use a shared primary key and 22 use a unique foreign key. The other 5 enforce nothing at all: the diagram draws a one-to-one line, and the exported DDL accepts as many child rows as anyone inserts. That gap, between the line you draw and the constraint you ship, is the main thing this post covers.

Should you split a table into a one-to-one relationship?

Usually not. Two tables in a strict one-to-one, where every row exists on both sides and every query reads both, are one table with an extra join. Normalization gives you no reason to split them, because no dependency changes when you merge them.

The split pays off when the two halves differ in some way the database can act on. The gallery's 43 one-to-one links fall into four groups.

Facts most rows don't have. On the Airbnb schema, most users never list a home, so payout details, response rate and the superhost flag live in hosts, keyed by user_id. A guest has no hosts row, and users carries no nine permanently-null columns. The IT helpdesk schema does the same with change_requests: risk level, rollback plan and test plan only exist for the minority of tickets that are changes.

Columns that are written much more often than the rest. The podcast app schema keeps playback position in playback_states, one row per episode, because the player writes it every few seconds while the episode's title and description never change. We measured why that matters on PostgreSQL below.

Data with different access rules. The password manager keeps TOTP seeds in totp_secrets, separate from items, so the code that lists a vault never reads a second-factor secret. Separate tables let you grant, audit, encrypt or back them up separately.

Subtypes. On the GitHub schema, a pull request is an issue with code attached: pull_requests.issue_id is the primary key and points at issues.id. Labels attach to the issue, while reviews and review comments attach to the pull request.

There is a fifth group that isn't a split at all: a business rule that says "at most one". One booking per appointment slot, one satisfaction rating per ticket, one postmortem per incident. Those are separate entities with a uniqueness rule between them, and they use the same unique foreign key.

Which table gets the foreign key in a one-to-one relationship?

The table whose rows can't exist without the other one. That is the optional side, and usually the one created later. A host can't exist without a user, so hosts holds user_id. If you put host_id on users instead, the column is null for every guest and you need a nullable unique column to enforce the rule, which is where the dialect differences below start.

The exception is a pointer from a parent to one of its own children. On the news paywall schema, articles.live_revision_id points to the revision readers currently see, and publishing swaps the pointer. The article owns many revisions, and only one is live. That is a many-to-one from revisions to articles plus a nullable pointer back, and the pointer column still needs a UNIQUE if two articles must never share a live revision.

Shared primary key or unique foreign key?

Use a shared primary key when the child is purely an extension of the parent and nothing will ever reference it on its own. This is drawDB's PostgreSQL export of hosts, trimmed to the key and two columns:

CREATE TABLE "users" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "email" VARCHAR(320) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS "hosts" (
  "user_id" BIGINT,
  "is_superhost" BOOLEAN NOT NULL DEFAULT false,
  "payout_currency" CHAR(3) NOT NULL DEFAULT 'USD',
  PRIMARY KEY("user_id")
);

ALTER TABLE "hosts"
ADD FOREIGN KEY("user_id") REFERENCES "users"("id")
ON UPDATE CASCADE ON DELETE CASCADE;

The primary key makes a second host row for the same user impossible, and it's the only index the table needs. A join between users and hosts matches id to user_id on two primary key indexes, and on MySQL's InnoDB, which stores rows in primary key order, both lookups land directly on the row.

Use a unique foreign key when the child has a life of its own. It might be referenced by other tables, moved to a different parent, or have its foreign key set to null. This is the export of change_requests:

CREATE TABLE "tickets" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "ticket_number" VARCHAR(24) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS "change_requests" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY,
  "ticket_id" BIGINT NOT NULL UNIQUE,
  "risk_level" VARCHAR(16) NOT NULL DEFAULT 'medium',
  "rollback_plan" TEXT,
  PRIMARY KEY("id")
);

ALTER TABLE "change_requests"
ADD FOREIGN KEY("ticket_id") REFERENCES "tickets"("id")
ON UPDATE CASCADE ON DELETE CASCADE;

That costs one more index than the shared key, and it's the right shape whenever you're unsure. Going from a unique foreign key to a shared primary key later means rewriting every table that references the child's id. Adding a surrogate id to a shared-key table later is a single-table change, so in practice either direction is recoverable. Pick the shared key when the child is clearly a set of extra columns, and the unique foreign key otherwise.

Travel

Airbnb database schema

How a short-term rental marketplace models its data: hosts and listings, a host-owned per-night calendar, request-to-book versus instant book, reservations, guest payments and host payouts, and two-way reviews that only reveal once both sides have written.

Why does my one-to-one relationship allow duplicates?

Because the diagram's cardinality isn't a constraint. In drawDB, as in most diagram tools, the one-to-one notation on a relationship line is a label for the reader. What reaches the database is the column: drawDB's SQL exporters read the field's primary key and unique flags, and ignore the cardinality setting entirely.

Five of the gallery's one-to-one lines have neither flag. The expense tracker's transfers table links an outflow transaction to an inflow transaction, drawn as one-to-one on both legs, and this is what the exporter generates for it:

CREATE TABLE "transactions" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "amount_minor" INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS "transfers" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY,
  "from_transaction_id" INTEGER,
  "to_transaction_id" INTEGER,
  "from_amount_minor" INTEGER NOT NULL,
  "to_amount_minor" INTEGER NOT NULL,
  PRIMARY KEY("id")
);

ALTER TABLE "transfers"
ADD FOREIGN KEY("from_transaction_id") REFERENCES "transactions"("id")
ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE "transfers"
ADD FOREIGN KEY("to_transaction_id") REFERENCES "transactions"("id")
ON UPDATE CASCADE ON DELETE SET NULL;

Nothing stops one transaction from being the outflow leg of two transfers, while the transaction's own transfer_id column can only name one of them, so the two sides of the link disagree. The fix is two constraints:

ALTER TABLE "transfers" ADD UNIQUE ("from_transaction_id");
ALTER TABLE "transfers" ADD UNIQUE ("to_transaction_id");

The opposite mistake exists too. 13 more references in the gallery are drawn as many-to-one even though the foreign key is unique or is the primary key, so the database enforces a one-to-one the diagram doesn't show. That one is harmless to the data, but it misleads whoever reads the diagram. When you mark a relationship one-to-one in drawDB, tick Unique on the foreign key column in the same edit, or make it the primary key.

How do NULLs behave in a one-to-one unique column?

The answer depends on the database, and it matters whenever the foreign key is nullable, for example with ON DELETE SET NULL.

SQL Server is the odd one out, and it breaks a common design. The subscription box schema's referrals.referred_member_id is unique and set to null when the referred member is deleted. On SQL Server, a plain UNIQUE on that column makes the first deletion succeed and the second fail with a duplicate key error, because both rows now hold NULL. Use a filtered unique index instead, which only covers rows where the column has a value:

CREATE TABLE [referrals] (
  [id] BIGINT IDENTITY PRIMARY KEY,
  [referrer_id] BIGINT NOT NULL,
  [referred_member_id] BIGINT NULL
);

CREATE UNIQUE INDEX [ux_referrals_referred_member]
ON [referrals] ([referred_member_id])
WHERE [referred_member_id] IS NOT NULL;

A shared primary key never runs into this, because a primary key column can't be null. That's one more reason to put the key on the optional side of the relationship.

How do you make both sides of a one-to-one mandatory?

Sometimes neither row should exist without the other. On the Dropbox-style schema, every user has a root folder and every folder has an owner. With a NOT NULL foreign key in both directions, you can't insert either row first.

PostgreSQL, SQLite and Oracle can check a foreign key at commit time instead of at each statement. Declare it DEFERRABLE INITIALLY DEFERRED and insert both rows in one transaction:

CREATE TABLE "users" (
  "id" BIGINT PRIMARY KEY,
  "root_folder_id" BIGINT NOT NULL UNIQUE
);

CREATE TABLE "folders" (
  "id" BIGINT PRIMARY KEY,
  "owner_id" BIGINT NOT NULL REFERENCES "users"("id")
);

ALTER TABLE "users"
ADD FOREIGN KEY ("root_folder_id") REFERENCES "folders"("id")
DEFERRABLE INITIALLY DEFERRED;

BEGIN;
INSERT INTO "users" ("id", "root_folder_id") VALUES (1, 100);
INSERT INTO "folders" ("id", "owner_id") VALUES (100, 1);
COMMIT;

Both ids are known before the inserts, so this works with UUIDs or with ids taken from a sequence first. MySQL checks foreign keys immediately, and SQL Server has no deferrable constraints. On those, leave one side nullable, insert both rows in one transaction, and accept that the database only enforces the mandatory part on one side. That's what the gallery's Dropbox schema does: users.root_folder_id is nullable, and its column note says it's null only between the two inserts.

Before you reach for this, ask whether two rows that must always exist together should be one row. The root folder survives as a separate row because folders form a tree and the root is a node in it. Most mandatory-on-both-sides pairs have no such reason.

What does splitting a hot column out cost on PostgreSQL?

PostgreSQL never updates a row in place. An UPDATE writes a new version of the whole row, so changing a 4-byte integer on a 1 KB row copies the whole row. We built the podcast case on PostgreSQL 16: 100,000 episodes with a description, an audio URL and the other usual columns, once with the playback position on the episode row and once with it in a one-to-one playback_states table.

That's 7.5 times less WAL for the same change. WAL is what your replicas replay and your backups store, so the difference shows up there too. The table that gets rewritten is 36 bytes wide instead of a kilobyte, so it stays in cache.

The cost is on the read side: showing an episode with its progress is now a join, which adds one primary key lookup per episode. For a list of 200 episodes that's 200 index probes and about 800 buffer hits, cheap next to what the write side saves. If both halves are written at the same rate, the split buys you nothing and you pay for the join anyway.

Media & content

Local-first podcast app database schema (SQLite)

How a podcast client stores its data on the device: subscribed shows, episodes mirrored from RSS feeds, playback positions, download state, an ordered queue, per-show settings, feed sync cursors and an offline outbox.

How do you draw a one-to-one relationship in an ER diagram?

In crow's foot notation, a one-to-one has a single bar on each end, or a ring and a bar on the side that's optional ("zero or one"). In drawDB, open the relationship, set the cardinality to one-to-one, and make sure the foreign key column is unique or is the primary key. The second step is the one that reaches your database.

The YouTube schema is a clean example to start from: video_monetization is a shared-key extension of videos, split off because it changes on a different schedule and is read only by the ad path. Open it in the editor, then export the SQL for your dialect and check that the UNIQUE or PRIMARY KEY is there.

Media & content

YouTube video platform database schema

How a video platform models its data: channels and uploads, encoded renditions, view counts rolled up per day, likes, one-level comment threads, subscriptions, playlists, monetisation and community posts.

Frequently asked questions