CODEWITHSUNDEEP
javasqlmysql-workbench
ASD
ASD

Reputation: 735

Identifying and non-identifying relationships

Cannot undestand main idea of identifying relationships:

An identifying relationship is when the existence of a row in a child table depends on a row in a parent table. This may be confusing because it's common practice these days to create a pseudokey for a child table, but not make the foreign key to the parent part of the child's primary key. Formally, the "right" way to do this is to make the foreign key part of the child's primary key. But the logical relationship is that the child cannot exist without the parent.

Example: A Person has one or more phone numbers. If they had just one phone number, we could simply store it in a column of Person. Since we want to support multiple phone numbers, we make a second table PhoneNumbers, whose primary key includes the person_id referencing the Person table.

But how can for example Person can have multiples phone numbers if person_id is primary key? Or possible person_id must be part of complex primary key and cannot separately identify items in table?

Upvotes: 0

Views: 249

Answers (1)

ruakh
ruakh

Reputation: 183371

Or possible person_id must be part of complex primary key […]?

Exactly. In the example you quote, the primary key for PhoneNumbers could be something like (person_id, number). This means that no two records PhoneNumbers could have the same person_id and number; they can have the same person_id, and they can have the same number, but not both at once. (In other words, the person_id and the number, taken together, "uniquely identify" a record in PhoneNumbers.)

Upvotes: 2

Related Questions