StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<ul> <li><p>An <strong>identifying relationship</strong> 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 <em>not</em> 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.</p> <p>Example: A <code>Person</code> has one or more phone numbers. If they had just one phone number, we could simply store it in a column of <code>Person</code>. Since we want to support multiple phone numbers, we make a second table <code>PhoneNumbers</code>, whose primary key includes the <code>person_id</code> referencing the <code>Person</code> table. </p> <p>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 include <code>person_id</code> in the primary key of <code>PhoneNumbers</code>).</p></li> <li><p>A <strong>non-identifying relationship</strong> is when the primary key attributes of the parent <em>must not</em> become primary key attributes of the child. A good example of this is a lookup table, such as a foreign key on <code>Person.state</code> referencing the primary key of <code>States.state</code>. <code>Person</code> is a child table with respect to <code>States</code>. But a row in <code>Person</code> is not identified by its <code>state</code> attribute. I.e. <code>state</code> is not part of the primary key of <code>Person</code>.</p> <p>A non-identifying relationship can be <strong>optional</strong> or <strong>mandatory</strong>, which means the foreign key column allows NULL or disallows NULL, respectively.</p></li> </ul> <hr> <p>See also my answer to <a href="https://stackoverflow.com/questions/2814469/still-confused-about-identifying-vs-non-identifying-relationships/2814663#2814663">Still Confused About Identifying vs. Non-Identifying Relationships</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload