What's the difference between identifying and non-identifying relationships?
I think this particular answer seems is the most succinct one.
- 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 ofPerson
. Since we want to support multiple phone numbers, we make a second tablePhoneNumbers
, whose primary key includes theperson_id
referencing thePerson
table.We may think of the phone number(s) as belonging to a person, even though they are modeled as attributes of a separate table. This is a strong clue that this is an identifying relationship (even if we don't literally includeperson_id
in the primary key ofPhoneNumbers
). - A non-identifying relationship is when the primary key attributes of the parent must not become primary key attributes of the child. A good example of this is a lookup table, such as a foreign key on
Person.state
referencing the primary key ofStates.state
.Person
is a child table with respect toStates
. But a row inPerson
is not identified by itsstate
attribute. I.e.state
is not part of the primary key ofPerson
.A non-identifying relationship can be optional or mandatory, which means the foreign key column allows NULL or disallows NULL, respectively.
No comments:
Post a Comment