CODEWITHSUNDEEP
databasedatabase-designforeign-keysrelational-databasecomposite-primary-key
user14229538
user14229538

Reputation:

Can weak entity has more than one primary key if it has more than one strong entity?

If I have a weak entity with no attributes and has a four weak relationship with the owner entities and I want to design the relational schema for this weak entity, Can I take all primary keys of the strong entities that have relations with the weak entity and make them as primary keys for the weak entity? I add an example below, my question is about the "Recon" entity.

ER diagram(Recon entity)

Upvotes: 0

Views: 1457

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Migrate the primary keys of the related strong entities into the primary key of the weak entity. The result is one (composite) primary key, not multiple primary keys1.

The resulting table would look something like this:

CREATE TABLE Recon (

    sin int REFERENCES Person,
    method int REFERENCES Method,
    name int REFERENCES Place,
    time REFERENCES TimeSlot,

    PRIMARY KEY (sin, method, name, time)

    -- Other fields and constraints...

);

NOTE: I'd consider introducing surrogate keys into the parent/strong entities, simply to make the weak entity's key leaner (e.g. introduce PlaceId and reference it from Recon instead of the natural key name).

1 In fact, there can be only one primary key per entity, although you could in theory have multiple alternate keys.

Upvotes: 0

Related Questions