Identifying or non-identifying? Separate identity from required participation
Compare order-line keys, required foreign keys, delete rules, and UNIQUE constraints when a child receives a surrogate id.
A relationship is identifying when the parent key contributes to the child's primary key. An order line identified by (order_id, line_number) cannot be named without its order, so the order relationship is identifying. A customer id on an order is usually non-identifying: it tells us who placed the order but does not make up the order's identity.
The choice changes constraints as well as notation. An identifying foreign key is normally NOT NULL and belongs to the child primary key. A non-identifying foreign key can still be required; it simply stays outside the primary key. Do not select the type to make the diagram look tidy—ask whether the child can be identified independently of its parent.
Two valid identities for an order line
With PRIMARY KEY (order_id, line_no), the order id participates in the child's identity. With PRIMARY KEY (order_item_id), it does not. Both designs can require a parent order. A global line id is useful when support tools, shipments, or refunds address a line directly, but it does not remove the rule that a line number is unique within an order.
CREATE TABLE order_lines (
order_item_id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
line_no INTEGER NOT NULL,
UNIQUE (order_id, line_no),
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);This fragment assumes an existing orders table with an order_id primary key. The foreign key is written as a table constraint so MySQL actually enforces the reference. Do not rely on an inline column-level REFERENCES clause in MySQL.
Try four different invalid or valid rows
| Attempt | Expected result | Rule |
|---|---|---|
| Same order and line number, new global id | Rejected | UNIQUE (order_id, line_no) |
| Different order, same line number | Accepted if parent exists | Line number is local to an order |
| Missing order id | Rejected | NOT NULL |
| Unknown order id | Rejected | FOREIGN KEY |
These checks answer different questions. A UNIQUE failure says the business slot is already occupied. A foreign-key failure says the parent does not exist. Neither should be diagnosed by looking only at whether a line is solid or dashed.
Delete policy and optionality are separate decisions
An identifying foreign key cannot be NULL because it is part of the primary key. The reverse is false: a NOT NULL foreign key does not have to be identifying. Similarly, identifying does not automatically mean ON DELETE CASCADE. An order retained for audit may require restricted deletion even when its lines use a composite key.
In YourERD, check the child's PK, FK, and NN flags after selecting a relationship. The identifying marker is the small UID bar. Required participation and the inclusion of a parent key in a child identifier should be reviewed separately. Record any additional uniqueness rule alongside the diagram.
Reference and next step
Examples use simplified business rules for teaching. Check the database documentation for the exact enforcement behavior of your chosen engine.
Open the ecommerce sample in YourERD · Explore the sample models