Crow's-foot cardinality: 0..N, 1..1, and SQL nullability

In a crow's-foot ERD, read the symbol at one table as the number of rows from that table allowed for one row at the opposite table. A circle means the minimum is zero, a bar means the minimum is one, and a crow's foot means the maximum is many. For SQL, a NOT NULL foreign key can require every child row to name one parent, but that same constraint does not require every parent row to have a child.

Scope and terms before reading the feet

This note connects a logical crow's-foot model to a small PostgreSQL 18 schema. It covers minimum and maximum cardinality, foreign-key nullability, and referential integrity. It does not claim that every business rule visible in a diagram is automatically enforceable by one foreign key.

Oracle's SQL Developer Data Modeler guide defines cardinality as the number of occurrences of one entity for one occurrence of the related entity. Its relation properties keep two decisions separate at each endpoint: whether the count is one or many, and whether zero instances are permitted. That separation produces the four common ranges:

The safest reading prompt is: for one row over here, how many rows over there may participate? Look at the endpoint beside “over there” for the answer. Then reverse the question and read the other endpoint. “One-to-many” alone is incomplete because it omits both minimums.

Worked policy: required customer, optional representative

The fictional sales system has three stated rules:

1. A customer may exist before placing an order, so one Customer has 0..N SalesOrders.
2. Every SalesOrder belongs to exactly one Customer, so one SalesOrder has 1..1 Customer.
3. Assignment is optional: one SalesOrder has 0..1 SalesRep, while one SalesRep has 0..N SalesOrders.

No rule says that a customer must eventually order, that a representative must handle an order, or that an order may have multiple representatives. Those are not omissions to guess around; they determine the endpoint symbols.

Reproducible Schematex source

erd
title: Customer order cardinalities
direction: LR

table Customer {
  customer_id int PK
  name text NN
}

table SalesRep {
  sales_rep_id int PK
  name text NN
}

table SalesOrder {
  order_id int PK
  customer_id int FK NN -> Customer.customer_id
  sales_rep_id int FK -> SalesRep.sales_rep_id
  total_cents integer NN
}

ref SalesOrder.customer_id many-optional .. one-mandatory Customer.customer_id : placed by
ref SalesOrder.sales_rep_id many-optional .. one-optional SalesRep.sales_rep_id : handled by

Schematex 1.0.14 strictly parsed and rendered this source with zero diagnostics. Its current ERD syntax reference names the endpoints many-optional, one-mandatory, and one-optional. Both lines are dashed because these are non-identifying relationships: neither foreign key is part of SalesOrder's primary key. Identifying versus non-identifying is a separate decision from mandatory versus optional.

Read the first ref from right to left. The endpoint beside SalesOrder is many-optional, so one Customer can relate to zero or many orders. Read it left to right. The endpoint beside Customer is one-mandatory, so each SalesOrder requires exactly one customer.

The second ref keeps many-optional beside SalesOrder, but uses one-optional beside SalesRep. One representative can handle zero or many orders; one order can have zero or one representative.

DDL that enforces the child-side rules

CREATE TABLE customer (
  customer_id integer PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE sales_rep (
  sales_rep_id integer PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE sales_order (
  order_id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customer (customer_id),
  sales_rep_id integer REFERENCES sales_rep (sales_rep_id),
  total_cents integer NOT NULL CHECK (total_cents >= 0)
);

PostgreSQL 18 defines a foreign key as a requirement that a non-null referencing value match a row in the referenced table. It defines NOT NULL separately as a prohibition on null values. Combining them on sales_order.customer_id implements the order-to-customer 1..1 minimum and match: the value must exist, and it must identify a real Customer. Leaving sales_rep_id nullable implements the order-to-representative minimum of zero; if the value is non-null, the foreign key still requires a real SalesRep.

Six row tests, with expected results

Run these statements in order against an empty copy of the three tables. In a client that wraps the whole script in one transaction, use a savepoint around each intentionally failing insert so one expected error does not abort the remaining checks.

INSERT INTO customer VALUES (101, 'Northwind'), (102, 'No orders yet');
INSERT INTO sales_rep VALUES (901, 'A. Diaz');

INSERT INTO sales_order VALUES (5001, 101, NULL, 2500);  -- accepted
INSERT INTO sales_order VALUES (5002, NULL, NULL, 2500); -- NOT NULL error
INSERT INTO sales_order VALUES (5003, 999, NULL, 2500);  -- customer FK error
INSERT INTO sales_order VALUES (5004, 101, 999, 2500);   -- sales_rep FK error
INSERT INTO sales_order VALUES (5005, 101, 901, 4000);   -- accepted

After all six tests, Customer 101 has two accepted orders and Customer 102 has zero. SalesOrder 5001 has exactly one customer and zero representatives; SalesOrder 5005 has exactly one customer and one representative. The rejected rows leave no partial relationship behind.

The important counterexample is Customer 102. The database accepts that parent without a child even though every child must have a parent. A child-side NOT NULL REFERENCES constraint points only one way: it constrains each SalesOrder row. It does not assert that every Customer appears in sales_order.customer_id.

Checks and invariants

Use this review ledger before approving a crow's-foot ERD and its DDL:

1. For each relationship, write two sentences—one in each direction—with explicit min..max values.
2. Every 1..1 parent count per child has a non-null referencing column or an equivalent reviewed constraint.
3. Every 0..1 parent count per child permits null and still has a foreign key for non-null values.
4. Every foreign key targets a primary key, unique constraint, or other database-supported unique target.
5. A 0..N endpoint is tested with a parent that has no children, not only with populated examples.
6. A 1..N endpoint is not claimed unless the implementation has a reviewed way to prevent a childless parent.
7. Identifying line style agrees with key structure; mandatory participation alone does not make a relationship identifying.
8. Delete behavior, update behavior, and indexes are reviewed separately; cardinality glyphs do not specify them.

For a negative test, change the endpoint beside SalesOrder on the Customer relationship from many-optional to many-mandatory. The diagram would then claim every Customer has at least one SalesOrder, but the DDL would still accept Customer 102 with none. That deliberate mismatch is a compact test of whether a reviewer is checking semantics or merely recognizing a familiar foot shape.

Failure modes the clean diagram does not solve

Reading the endpoint from its own table. The foot beside SalesOrder answers “orders per one Customer,” not “customers per one SalesOrder.” Always start the sentence at the opposite entity.

Treating FK as automatically mandatory. PostgreSQL permits null in a foreign-key column unless NOT NULL is also declared. A nullable foreign key means “zero or one matching parent,” not “any arbitrary value.”

Treating child mandatory as parent mandatory. Requiring every order to have a customer does not require every customer to have an order. Enforcing parent participation of 1..N usually needs a controlled transaction or procedure, a deferred constraint trigger, or another design whose insertion and deletion lifecycle is reviewed explicitly.

Confusing line style with minimum count. In this example, dashed lines mean the parent key does not form part of the child's primary key. Solid versus dashed does not replace the circle, bar, or crow's foot.

Inferring cascade or performance behavior. The two endpoints say how many related rows are allowed. They do not choose ON DELETE, ON UPDATE, or an index. PostgreSQL notes that a foreign key does not automatically create an index on the referencing columns.

Reproduce the two-direction check

Paste the source into the Schematex playground and read both relationships aloud in both directions. Then change only sales_rep_id from nullable to NN: the DDL intent becomes one required representative per order, so the SalesRep endpoint must also change from one-optional to one-mandatory. Finally, run the six row tests and keep Customer 102 as the proof that a child-side mandatory foreign key does not turn parent-side 0..N into 1..N.