One-to-One Database Relationships

A one-to-one relationship links each row of a table to at most one row of another: one user, one settings row; one order, one payment receipt. In the diagram it is an edge with a single marker on both ends.

How to express 1:1 in SQL

The recipe: put the foreign key on the dependent table and make it UNIQUE. A UNIQUE foreign key can point at each parent row at most once — that constraint is what turns 1:N into 1:1.

CREATE TABLE user_settings (
  user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
  theme VARCHAR(20) NOT NULL DEFAULT 'dark',
  notifications BOOLEAN NOT NULL DEFAULT true
);

Read the constraints in order. NOT NULL says a settings row must belong to someone. UNIQUE says a user cannot have two settings rows. REFERENCES says the user must exist. Together they encode 'at most one settings row per user, and every settings row belongs to exactly one user'.

The shared-primary-key variant

A second implementation makes the foreign key the child table's primary key. Because a primary key is already unique and never NULL, no separate UNIQUE constraint is needed, and the two rows end up sharing their identifier — an employee row and its payroll row both carry employee number 42.

CREATE TABLE employees (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  hired_on DATE NOT NULL
);

CREATE TABLE payroll_details (
  employee_id BIGINT PRIMARY KEY REFERENCES employees(id) ON DELETE CASCADE,
  bank_account VARCHAR(34) NOT NULL,
  monthly_salary NUMERIC(10,2) NOT NULL
);

This variant reads well in a diagram — payroll_details sits directly opposite employees with a plain 1:1 edge — and it makes joins trivial since the join key is right there. Its limitation is that a parent row can only ever have one dependent row in that table. If you later need two payroll records per employee (a history, say), you have to rework the table. The UNIQUE-FK variant generalizes better; the shared-PK variant is neater when the pairing is truly permanent.

When 1:1 is actually the right call

• Optional heavy data: split a rarely-read blob into its own table so the main table stays lean.
• Different access rights: store secrets in a table only some code can query.
• Genuinely different things that happen to pair up: users and their OAuth identities per provider.

Each of these has a concrete shape. A products table where only 2% of rows carry a long description and an image set: putting those columns inline makes every full-table scan drag the dead weight along, while a product_details table keeps the hot path narrow. A payroll_details table restricted by GRANT statements so the general application role cannot SELECT from it — table-level isolation is simpler to audit than column-level permissions. Or per-provider OAuth identities, where the pairing (user, provider) is one row by definition.

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  price_cents INTEGER NOT NULL
);

CREATE TABLE product_documents (
  product_id BIGINT NOT NULL UNIQUE REFERENCES products(id) ON DELETE CASCADE,
  spec_pdf BYTEA,
  manual_pdf BYTEA,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

If neither reason applies, the columns usually belong on the main table. A 1:1 split you cannot justify is a JOIN you pay for on every read. In ERDGenerator, draw the edge, set both cardinalities to 1, and the label will read 1:1 — useful when documenting, for example, that each hotel booking carries exactly one payment record.

Optional vs mandatory 1:1

There is a subtlety worth naming: 'at most one' and 'exactly one' are different constraints. The tables above all allow a user (or product, or employee) with no companion row — the 1:1 is optional on the parent side. If the pairing is mandatory, plain constraints cannot express it: a NOT NULL FK in both directions can never be satisfied by ordinary inserts, so it takes a deferred constraint or an application-level check. Most real 1:1s are optional, and documenting them as '0..1 on both ends' is more honest than claiming an exact match the schema does not guarantee.

How the diagram shows it

A 1:N edge and a 1:1 edge differ only in cardinality markers, so it is easy to misread one for the other at a glance. The tell is in the child table's columns: a 1:1 shows a FK that is also UNIQUE (or is itself the PK). When reviewing a generated diagram, click into the child table and look for that UNIQUE badge — it is the difference between 'one settings row per user' and 'a settings log with many entries per user', which is a very different system.

Common mistakes

• Forgetting the UNIQUE on the FK column. The schema then silently allows multiple child rows per parent, and the diagram correctly shows 1:N — while the code assumes 1:1 and breaks on the second row.
• Splitting a table 1:1 'for cleanliness' when the child row is always fetched with the parent. You pay an extra JOIN on every read for no benefit.
• Putting the FK on the wrong side. It belongs on the dependent table; if you delete a parent and its 1:1 companion survives, the FK is on the wrong table or the delete behavior is wrong.
• Assuming 1:1 means 'exactly one'. Without extra machinery, SQL enforces 'at most one'. Design the inserts so the companion row is created in the same transaction as the parent when it is mandatory.
• Forgetting ON DELETE CASCADE (or the equivalent cleanup) so orphaned companion rows accumulate silently.
• Modeling something as 1:1 that is really 1:0..N. Payment attempts are the classic case: the first charge fails, the second succeeds — that is 1:N, and a UNIQUE FK will reject the second attempt.

Best practices

• Default to keeping columns on the main table; split into a 1:1 only for one of the three legitimate reasons — volume, access control, or a genuinely separate concept.
• Prefer the UNIQUE-FK variant unless identifier-sharing is the point; it is easier to change later.
• Create both rows in a single transaction when the pairing is mandatory, so a crash never leaves one half behind.
• Give the companion table a FK column named after the parent (user_id, product_id, employee_id) — it makes generated diagrams and generated code read naturally.
• Add ON DELETE CASCADE unless the companion row carries audit value; if it does, use RESTRICT and delete it out-of-band.
• Document the cardinality in the diagram, not just in the schema: a 1:1 edge that says so saves the next reader a trip through the DDL.

Do I need a 1:1 table at all?

Run the three-question test. Is the extra data optional or heavy? Does it need different permissions? Is it a separate concept with its own lifecycle? If all answers are no, keep the columns inline. If exactly one is yes, the split is probably justified. If unsure, start inline — splitting a table later is a routine migration, while merging one is disruptive.

How is 1:1 different from just two columns on one table?

Functionally it often is the same data. The difference is mechanical: a separate table gets its own physical storage, its own indexes, and its own permissions, and rows there can be large or numerous without touching the parent. If you are splitting to reduce per-row size on a hot table, or to wall off sensitive columns, the separation does real work. If you are splitting 'for tidiness', you are adding a JOIN to every query that reads those fields.

Which side holds the foreign key in a 1:1?

The dependent side — the row that cannot sensibly exist without the other. user_settings depends on users, so it carries the FK. If you genuinely cannot say which side depends on which, that is a strong sign the two tables should be one table, or that the relationship is not really 1:1.