Identifying vs non-identifying relationships, finally explained

October 14, 20266 min read

Crow's foot notation answers how many - one, many, optional. But every line on an ER diagram quietly answers a second question the endings don't cover: where does the child's identity come from? That's the identifying vs non-identifying distinction, it's the vocabulary every modeling tool assumes you know, and it decides what your child table's primary key looks like in SQL.

One comparison makes it click:

The same one-to-many cardinality drawn twice: identifying with a solid line and a composite primary key, non-identifying with a dashed line and the parent's key as a plain foreign key

The identifying case: a line item has no life outside its order

Take orders and order_items. What is one line item? It's "line 3 of order #1042" - and that's not a coincidence of naming, it's the entire identity. "Line 3" means nothing on its own; every order has its own line 3. So the primary key of order_items is (order_id, line_no) - the parent's key is inside the child's key.

That's an identifying relationship: the child is identified by its connection to the parent. The child table is called a weak entity - not weak as in broken, weak as in it can't stand alone. On diagrams that distinguish them (the classic crow's foot / IE convention), identifying relationships draw as a solid line.

Read the DDL and you can hear the difference:

CREATE TABLE order_items (
  order_id  bigint NOT NULL REFERENCES orders(id),
  line_no   int    NOT NULL,
  qty       int    NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

The foreign key isn't just a column here - it's half the primary key. That's the whole definition in one line of SQL.

The non-identifying case: an order exists on its own

Now users and orders. An order is a thing - it has an id of its own, a lifecycle of its own. The user is a fact about the order, not a part of its address in the schema. The primary key of orders is just (id); user_id sits there as a plain foreign-key column.

That's non-identifying, drawn as a dashed line in the classic notation. It's also - plot twist - almost every relationship you'll ever draw.

The one-question test

When you're sketching and can't remember the terms, ask:

Could this child row make sense if you forgot which parent it belonged to?

A line item without its order? Nonsense - identifying. An order without its user? Weird business-wise, but the row still coheres - it has an amount, a date, a status - non-identifying. The test is about identity, not about whether you'd allow the deletion. Those are different decisions, and mixing them up is the most common confusion around this vocabulary (more on that in the FAQ).

What this means for cardinality and for SQL

Two clarifications people need side by side:

Cardinality is untouched. Identifying and non-identifying relationships can both be 1:N, 1:1, anything - the crow's foot endings answer how many, the solid/dashed choice answers whose key. Two independent dials.
The SQL shape follows directly. Identifying → composite primary key containing the FK (and usually ON DELETE CASCADE, since deleting the parent orphans rows that can't exist anyway - delete rules deserve their own post). Non-identifying → surrogate key plus a plain FK, with whatever delete rule the business actually wants.

One warning shot: identifying chains compound. If order_items is identified by its order, and something is identified by order_items, your grandchild's primary key is now three columns wide - and the fourth level is four. One level of identifying is a clean, expressive choice; a chain of them is how schemas end up with six-column primary keys nobody can write a migration for. (The classic junction table gets away with a two-FK composite key for exactly this reason - and stops there.)

Side by side

	Identifying	Non-identifying
Child's primary key	includes the parent's key	its own (usually a surrogate `id`)
The FK column's role	part of the PK	plain column
Line style (IE notation)	solid	dashed
Child's fancy name	weak entity	regular (strong) entity
Child without a parent	meaningless	the row still coheres
Typical example	order → order_items, invoice → invoice_lines	user → orders, author → posts
How often you'll use it	occasionally, deliberately	almost always

Which one should your schema use?

Default to non-identifying - surrogate id keys, plain FK columns. It's what ORMs expect, it keeps key widths stable as the schema grows, and it never punishes you when a "line item" later needs to outlive its parent (refunds referencing archived orders will happen to you).

Reserve identifying for the genuinely line-item-shaped children: their rows are details of a parent document, ordered and numbered within it, with no existence outside it. There the composite key isn't ceremony - it enforces the business rule that line 3 of an order is unique, at the database level, for free.

The deeper point is the one the schema design basics post keeps making: keys are how a schema states its rules. An identifying relationship states "this child is a part of that parent" - and if that's true in your domain, saying it in the key is cheaper than policing it in application code.

Frequently asked questions

What is a weak entity in an ER diagram?

A table that can't be identified without its parent - its primary key includes the parent's key. Classic examples: order lines, invoice lines, exam answers (identified by student + question). The relationship to its parent is called identifying, and in classic notation it's drawn as a solid line.

What does a dashed line mean in crow's foot notation?

A non-identifying relationship: the child has its own primary key, and the parent's key appears in it only as a plain foreign key. A solid line means identifying - the parent's key is part of the child's primary key. The dashes say nothing about cardinality; read the line endings for that.

Should a junction table use a composite primary key?

Usually yes - the pair of foreign keys is the identity of the relationship, and the composite key automatically prevents duplicate pairs. It's the same identifying logic as order lines: the junction row has no meaning without both parents. Add a surrogate id only if other tables need to point at the relationship itself.

Does an identifying relationship require ON DELETE CASCADE?

No - they're separate decisions. Identifying says the child's identity depends on the parent; the delete rule says what happens to the rows when a parent goes away. They usually travel together (cascade feels natural for lines of an order), but you might restrict deletes instead, forcing anyone removing an order to move its lines first. Identity is about keys; the delete rule is a policy you choose.

Draw the line, then let the tool mind the endings

Whether a relationship is identifying is a design decision - the drawing should record it, not decide it. If you sketch in FigJam, FigSchema anchors every relationship to its exact columns, so the foreign-key structure underneath each line is always visible - and the crow's foot endings stay truthful while the diagram moves. Free, and offline.