Mysql: Still Confused About Identifying vs. Non-Identifying Relationships

So, I've been reading up on identifying vs. non-identifying relationships in my database design, and a number of the answers on SO seem contradicting to me. Here are the two questions I am looking at:

1. What's the Difference Between Identifying and Non-Identifying Relationships
2. Trouble Deciding on Identifying or Non-Identifying Relationship

Looking at the top answers from each question, I appear to get two different ideas of what an identifying relationship is.

The first question's response says that an identifying relationship "describes a situation in which the existence of a row in the child table depends on a row in the parent table." An example of this that is given is, "An author can write many books (1-to-n relationship), but a book cannot exist without an author." That makes sense to me.

However, when I read the response to question two, I get confused as it says, "if a child identifies its parent, it is an identifying relationship." The answer then goes on to give examples such as Social Security Number (is identifying of a Person), but an address is not (because many people can live at an address). To me, this sounds more like a case of the decision between primary key and non-primary key.

My own gut feeling (and additional research on other sites) points to the first question and its response being correct. However, I wanted to verify before I continued forward as I don't want to learn something wrong as I am working to understand database design. Thanks in advance.

 Answers

The technical definition of an identifying relationship is that a child's foreign key is part of its primary key.

CREATE TABLE AuthoredBook (
  author_id INT NOT NULL,
  book_id INT NOT NULL,
  PRIMARY KEY (author_id, book_id),
  FOREIGN KEY (author_id) REFERENCES Authors(author_id),
  FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

See? book_id is a foreign key, but it's also one of the columns in the primary key. So this table has an identifying relationship with the referenced table Books. Likewise it has an identifying relationship with Authors.

A comment on a YouTube video has an identifying relationship with the respective video. The video_id should be part of the primary key of the Commentstable.

CREATE TABLE Comments (
  video_id INT NOT NULL,
  user_id INT NOT NULL,
  comment_dt DATETIME NOT NULL,
  PRIMARY KEY (video_id, user_id, comment_dt),
  FOREIGN KEY (video_id) REFERENCES Videos(video_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

It may be hard to understand this because it's such common practice these days to use only a serial surrogate key instead of a compound primary key:

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  video_id INT NOT NULL,
  user_id INT NOT NULL,
  comment_dt DATETIME NOT NULL,
  FOREIGN KEY (video_id) REFERENCES Videos(video_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

This can obscure cases where the tables have an identifying relationship.

I would not consider SSN to represent an identifying relationship. Some people exist but do not have an SSN. Other people may file to get a new SSN. So the SSN is really just an attribute, not part of the person's primary key.

---

Re comment from @Niels:

So if we use a surrogate key instead of a compound primary key, there is no notable difference between use identifying or non-identifying relationship ?

I suppose so. I hesitate to say yes, because we haven't changed the logical relationship between the tables by using a surrogate key. That is, you still can't make a Comment without referencing an existing Video. But that just means video_id must be NOT NULL. And the logical aspect is, to me, really the point about identifying relationships.

But there's a physical aspect of identifying relationships as well. And that's the fact that the foreign key column is part of the primary key (the primary key is not necessarily a composite key, it could be a single column which is both the primary key of Comments as well as the foreign key to the Videos table, but that would mean you can store only one comment per video).

Identifying relationships seem to be important only for the sake of entity-relationship diagramming, and this comes up in GUI data modeling tools.

---

Identifying / non-identifying relationships are concepts in ER modelling - a relationship being an identifying one if it is represented by a foreign key that is part of the referencing table's primary key. This is usually of very little importance in relational modelling terms because primary keys in the relational model and in SQL databases do not have any special significance or function as they do in an ER model.

For example, suppose your table enforces two candidate keys, A and B. Suppose A is also a foreign key in that table. The relationship thus represented is deemed to be "identifying" if A is designated to be the "primary" key, but it is non-identifying if B is the primary key. Yet the form, function and meaning of the table is identical in each case! This is why in my opinion I don't think the identifying / non-identifying concept is really very important.

---

part of the issue here is the confusion of terminology. identifying relationships are useful for avoiding long join paths.

The best definition i have seen is "an identifying relationship includes the PK as of the parent in the the child PK. In other words the PK of the child includes the FK to the parent as well as the "actual" PK of the child.

---

identifying relationship gives out one to many optional relationship when we have to define parent to child relationship.in addition it gives one to only one relationship from child to parent flow.since parent entity primary key will be the part of primary key of child entity, child entity instance will identify the parent entity instance.it is represented by solid line in er diagram.

where as non identifying relationship will many to many relationship.For the existence of child entity instance there should have parent entity instance but each entity instance in child entity may be related to many entity instance of parent entity.this is the reason why primary key of parent entity well be the foreign key of child entity, but child entity will not take parent entity primary key as its primary key.it will have its own primary key. many to many relation doesn't exist in real world er diagram. so it need to be resolved