Weak Entity Types

A weak entity has no key of its own, so the database makes one for it by borrowing the owner's primary key and gluing it to a local partial key (a discriminator) that is only guaranteed unique within one owner — the composite of the two becomes the real primary key, and that borrowed owner key doubles as a foreign key enforcing existence dependency.

That single mechanism explains every textbook property at once. The borrowed owner key is why a weak entity cannot exist without its owner (the FK must point at a real row). The local discriminator being unique only per-owner is why it is called partial — it disambiguates siblings under one parent, not globally. And because identification is shared, the ER diagram draws the entity as a double rectangle and the owning relationship (the identifying relationship) as a double diamond, with the discriminator underlined by a dashed line instead of a solid one.

• Owner / strong entity — has a key that stands on its own.
• Partial key (discriminator) — unique only among siblings of the same owner.
• Identifying relationship — total participation on the weak side: every weak row must be tied to exactly one owner.

Worked example: Course and Course Section

Take a university with a strong Course (key CourseID) and a weak Section whose discriminator is SectionNo. Watch why SectionNo alone fails as a key. Two different courses each have a “Section 1”:

Row 1 and row 3 both carry SectionNo = 1. If SectionNo were the primary key, those two rows would collide — yet they are genuinely different sections of different courses. SectionNo is unique only inside one CourseID. The fix is the composite key (CourseID, SectionNo): (MATH101, 1), (MATH101, 2), and (CS210, 1) are all distinct, and (MATH101, 1) is what a human means by “Math 101, Section 1.”

Mapping the weak entity to relational tables

The ER picture turns into SQL by a fixed recipe: the weak entity becomes its own table whose primary key is (owner PK columns + discriminator), and the owner PK columns are simultaneously a foreign key back to the owner. There is no separate table for the identifying relationship — it is absorbed into that FK.

CREATE TABLE Course (
    CourseID    VARCHAR(8)  PRIMARY KEY,
    CourseName  VARCHAR(80) NOT NULL
);

CREATE TABLE Section (
    CourseID    VARCHAR(8)  NOT NULL,   -- borrowed owner key
    SectionNo   INT         NOT NULL,   -- partial key / discriminator
    Instructor  VARCHAR(80),
    Room        VARCHAR(16),

    PRIMARY KEY (CourseID, SectionNo),  -- composite identity
    FOREIGN KEY (CourseID)              -- same column enforces ownership
        REFERENCES Course(CourseID)
        ON DELETE CASCADE
);

Three properties fall out of these constraints, each tied back to the mechanism:

1. Existence dependency is the FOREIGN KEY ... REFERENCES Course with CourseID NOT NULL: a section row cannot be inserted unless its course already exists.
2. Per-owner uniqueness is the composite PRIMARY KEY (CourseID, SectionNo): it permits (MATH101,1) and (CS210,1) but rejects a second (MATH101,1).
3. Lifecycle coupling is ON DELETE CASCADE: drop a course and its sections vanish with it, which matches “cannot exist without the owner.”

Why the naive version is wrong. A common mistake is to give Section its own surrogate key and a plain FK:

-- WRONG for a true weak entity
CREATE TABLE Section (
    SectionID  INT PRIMARY KEY,        -- invented global key
    CourseID   VARCHAR(8),             -- nullable FK by default
    SectionNo  INT,
    Instructor VARCHAR(80)
);

This silently breaks the contract: CourseID is nullable, so an orphan section with no course can exist; and nothing stops two rows with the same (CourseID, SectionNo), so “Math 101, Section 1” can be duplicated. The surrogate key buys a smaller join column but throws away the identity rule the model was trying to enforce. If you do use a surrogate for ergonomics, you must add NOT NULL on the FK and a UNIQUE (CourseID, SectionNo) constraint to restore both guarantees.

Pitfalls

• Confusing a weak entity with a nullable child. A child that merely references a parent but has its own standalone key (e.g. Order referencing Customer) is not weak. It becomes weak only when it has no identity without the parent. Misclassifying drives you to wrong keys.
• Forgetting the discriminator must be unique per-owner, not globally. Engineers sometimes slap a UNIQUE constraint on SectionNo alone, which wrongly forbids two courses from each having a Section 1. The uniqueness lives on the pair.
• Surrogate key without the backstop constraints. Introducing SectionID for friendlier joins is fine, but if you drop the NOT NULL FK and the UNIQUE(CourseID, SectionNo), you reopen orphans and duplicates — the very bugs the weak-entity model prevents.
• FK cascade depth. ON DELETE CASCADE matches the semantics, but if the weak entity itself owns further weak entities (e.g. Section owns Meeting), a single owner delete can cascade through several tables. Verify the chain before enabling it in production.
• Composite FKs in child tables get verbose. Anything pointing at Section must carry both CourseID and SectionNo. Deep weak-entity hierarchies grow wide composite keys; this is the standard argument for sometimes preferring a surrogate at scale.

Takeaways

• A weak entity is identified by owner PK + partial key; that composite is its real primary key and is the whole reason for the double-rectangle / double-diamond notation.
• The relational mapping is mechanical: weak entity → its own table, PK = (owner key columns + discriminator), and those owner columns are also a NOT NULL foreign key — no separate table for the identifying relationship.
• The partial key is unique per owner, never globally; enforce it as a composite key, not a lone UNIQUE.
• A surrogate key is a legitimate convenience, but only if you re-add NOT NULL on the FK and a UNIQUE on (owner key, discriminator) to preserve existence dependency and per-owner uniqueness.

Sources: Elmasri & Navathe, Fundamentals of Database Systems (weak entity types, identifying relationships, partial keys, and the ER-to-relational mapping algorithm); Silberschatz, Korth & Sudarshan, Database System Concepts (weak entity sets and discriminators); and the SQL:2016 standard for foreign-key and composite primary-key semantics. Re-authored and deepened for this guide: the original page correctly explained the concept and the “Math 101, Section 1” composite-key reasoning; this version adds the traced multi-row example, the relational table mapping with composite PK/FK, a corrected note on the naive surrogate-key version, and the identifying-relationship-to-foreign-key diagram.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Weak Entity Types

Why this concept exists (judgment chain)

A weak entity has no standalone identity: its key is owner_PK + partial key. That composite is both the identity rule and the existence rule (owner columns are also NOT NULL FK). Confusing “nullable child with surrogate” with “weak entity” produces orphans and duplicate business keys.

Worked example with numbers or traced steps

Course(MATH101), Course(CS210); SectionNo alone is not unique:
  (MATH101,1), (MATH101,2), (CS210,1) — three distinct sections.
DDL: PRIMARY KEY (CourseID, SectionNo),
     FOREIGN KEY (CourseID) REFERENCES Course ON DELETE CASCADE.
Naive surrogate SectionID without UNIQUE(CourseID, SectionNo):
  allows two “Math 101 Section 1” rows and NULL CourseID orphans.
Child Enrollment referencing Section must carry BOTH CourseID and SectionNo.

When NOT to use / named alternative

Do not mark Order as weak of Customer — orders have their own natural/surrogate identity. Prefer pure composite when the business key is how humans name the entity; introduce surrogate only for join ergonomics, re-adding NOT NULL FK + UNIQUE(owner, discriminator). Skip CASCADE if soft-delete/audit requires surviving children.

Failure / ops fingerprint

Fingerprint: UNIQUE(SectionNo) alone rejects legitimate cross-course Section 1; deleting Course leaves orphan Sections; deep cascade chains wipe Meeting under Section unexpectedly. Ops: document cascade depth; test DELETE owner in staging; prefer RESTRICT until product confirms lifecycle coupling.

Hostile-panel drills (defend the decision)

Q1. What is a partial key?
Model answer: A discriminator unique only among siblings under one owner — not globally unique. Identity is (owner key + partial key).

Q2. Why is the identifying relationship absorbed into the FK?
Model answer: Weak entity table already embeds owner PK columns; no separate relationship table is needed for pure identifying 1:N.

Q3. Surrogate vs composite for Section — defend both.
Model answer: Composite encodes business identity and blocks dups. Surrogate shortens child FKs but must add UNIQUE(CourseID, SectionNo) and NOT NULL FK or you lose the weak-entity guarantees.