How do you map a weak entity — one whose instances are identified only inside an owner, such as a line within an invoice — into a relational table, and what is that table's primary key?
answer
- PK = owner key + discriminator
- owner columns are also the FK, not null
- uniqueness scoped per owner
- no reparenting by construction
- keys widen at each weak level
basics
~20 sIt becomes its own table whose primary key is the owner's primary key plus the weak entity's discriminator (for example invoice_id plus line_no). The owner-key columns are also a foreign key to the owner, declared not null because the dependency is mandatory.
solid answer
~60 sA weak entity maps to a table of its own. It takes its own attributes as columns, plus the owner's primary key. The primary key is the **owner key together with the discriminator** — (invoice_id, line_no) — which is the direct expression of "line numbers are unique only within one invoice". The owner-key columns simultaneously form a foreign key to the owner table and are not nullable, because a weak entity cannot exist without its owner. Deletion of the owner should propagate, since orphaned weak rows are meaningless — whether that is a cascade or an application rule is a policy choice, but leaving them behind is not. The composite key is not a cosmetic detail: it enforces the uniqueness scope, it makes the owner key available to children without an extra join, and it prevents a line being reparented, since changing the owner would change the row's identity. If the child later needs to be referenced from far away, or moved between owners, that is a signal it was never really weak and a surrogate key is warranted.
code
sql · 8 linesCREATE TABLE invoice_line (
invoice_id BIGINT NOT NULL REFERENCES invoice(invoice_id) ON DELETE CASCADE,
line_no INT NOT NULL,
product_id BIGINT NOT NULL REFERENCES product(product_id),
quantity INT NOT NULL,
unit_price NUMERIC(12,2) NOT NULL,
PRIMARY KEY (invoice_id, line_no)
);go deeper
Give the rule: its own table, primary key of owner key plus discriminator, foreign key to the owner.
Explain what the composite key enforces — uniqueness scoped per owner — and why the foreign key is not nullable and deletion must propagate.
Discuss multi-level key growth, when a surrogate is a justified physical deviation, and the concurrency question behind generating the discriminator.
Weigh faithful composite keys against referencing cost across services and messages, and decide where the per-owner uniqueness rule is enforced if it leaves the primary key.
## The rule A weak entity is one with no key of its own; its instances are identified only within an owner, using a partial key or discriminator. The mapping rule follows directly: create a table for the weak entity containing its own attributes plus the owner's primary key columns, and make the primary key the concatenation of the owner key and the discriminator. For invoice lines: the table holds invoice_id, line_no, and the line's own attributes, with primary key (invoice_id, line_no) and a foreign key on invoice_id. Because the existence dependence is total, the foreign key column is not nullable. ## What the composite key buys **Correct uniqueness scope.** The business rule is "line numbers restart in each invoice", and the composite key states exactly that. A surrogate key with no additional constraint would allow two rows with the same line number in the same invoice, which is a real defect that appears in production schemas. **The owner key is present in the child.** Children of the weak entity inherit a key that already carries the owner, so queries that filter by owner do not need to join back up. In deeper hierarchies this compounds, which is both the strength and the cost of the design. **Reparenting is impossible by construction.** Moving a line to another invoice would change its identity, which is precisely the claim weakness makes. If the business does want to move lines between invoices, the entity was not weak. ## Deletion A weak entity cannot outlive its owner, so deleting the invoice must remove its lines. This can be a cascading foreign key action, or an application or service rule, and reasonable teams differ. What is not acceptable is a schema where the owner can be deleted and the weak rows remain, because those rows are unidentifiable by definition. Note also that many systems never hard-delete such owners at all, and then the question turns into how status propagates rather than how rows are removed. ## Multi-level weakness A weak entity can itself own another weak entity, and the keys accumulate: (invoice_id, line_no, allocation_no). This is faithful to the model but the keys get wide, every child carries all ancestors' columns, and referencing such a row from an unrelated table means copying several columns. Beyond two levels this is where teams usually introduce a surrogate key on the child while keeping a unique constraint on (owner key, discriminator) so the real rule survives. That is a legitimate physical deviation from the mechanical mapping, not a modelling change. ## Discriminator generation The mapping says nothing about where line_no comes from, and it is worth thinking about. Per-owner sequential numbers cannot come from a global sequence, so they are usually assigned by the writer — which under concurrency needs either a lock on the owner or a retry on the unique violation. Candidates who mention this show they have implemented the pattern rather than only read about it. ## When not to map it this way The composite-key mapping is right when the child is genuinely local: referenced only through its owner, never moved, never externally identified. Once external systems need a stable handle on the row, or a message needs to name it in one field, a surrogate identifier earns its place. Keep the unique constraint on (owner key, discriminator) in that case; dropping it is how the original business rule quietly disappears from the schema.
- If you give the weak entity's table a surrogate primary key instead, what must you keep?A unique constraint on the owner key plus the discriminator, and a non-nullable foreign key to the owner. Without the unique constraint the schema no longer says line numbers are unique within an invoice, and duplicates appear. The surrogate is a convenience for referencing the row; it does not replace the rule the composite key was expressing.
- Where does the discriminator value come from under concurrent inserts?It is per-owner, so a global sequence cannot supply it. Typical approaches are to lock the owner row while computing the next number, or to attempt the insert and retry on a unique-constraint violation. Both are acceptable; what fails is reading the current maximum without any serialisation, which produces duplicates under concurrency.
saying these in an interview costs you the question
- Giving the child a surrogate key and dropping the unique constraint on owner plus discriminator
- Making the owner foreign key nullable despite the mandatory existence dependence
- Assuming the discriminator can come from a global sequence rather than being unique per owner
- Leaving weak rows behind when the owner is deleted
- Claiming a weak entity's row can be reassigned to another owner without changing its identity