Still Confused About Identifying vs. Non-Identifying Relationships

non identifying relationships database
identifying relationship symbol
examples of non identifying relationships
identifying relationship constraints
self recursive relationship in dbms
identify relationships between entities
identifying relationship in dbms in hindi
weak entity

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.

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 Comments table.

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.

Entity Relationship Model - Dartmouth CS, because each AccountType can be identified without having to exist in the parent table. In non-identifying relationships, the primary key of the child table is still included as a foreign key, but it doesn’t get to participate as a primary key. Hence, it is non-identifying. Here is an example for a 1:n relationship: Taking this a bit further, you’ll notice that in identifying relationships, it is really not possible to insert a record into the parent table without referencing the child table.

"as I don't want to learn something wrong".

Welll, if you really mean that, then you can stop worrying about ER lingo and terminology. It is imprecise, confused, confusing, not at all generally agreed-upon, and for the most part irrelevant.

ER is a bunch of rectangles and straight lines drawn on a piece of paper. ER is deliberately intended to be a means for informal modeling. As such, it is a valuable first step in database design, but it is also just that : a first step.

Never shall an ER diagram get anywhere near the preciseness, accuracy and completeness of a database design formally written out in D.

Data Modeling, How do you identify parent and child entities in a relationship? Identifying and Non-Identifying Relationships An identifying relationship means that the child table cannot be uniquely identified without the parent. For example, you have this situation in the intersection table used to resolve a many-to-many relationship where the intersecting table's Primary Key is a composite of the left and right (parents

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.

How can I Identify Parent and Child Table in a Database Relationship, will have the same value for the foreign key. Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity’s primary key.

I believe only difference between an identifying and non identifying relationship is about Nullability of the foreign key. If a FK cannot be NULL, the relationship it represents is identifying (child cannot exist without parent) else it is non identifying.

Still Confused About Identifying vs. Non-Identifying Relationships , The technical definition of an identifying relationship is that a child's foreign key is part of its primary key. CREATE TABLE AuthoredBook  A non-identifying relationship may not always be a relationship between a strong entity and a weak entity. There may exist a situation where a child itself has a primary key but existence of its entity may depend on its parent entity.

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, Because identifying relationships propagate keys to receiving entities and those in turn be resolved in order to minimize key length and confusion at the same time. If you are then still dissatisfied with the depth of insights revealed initially, and In nonidentifying relationships the primary key attributes of the parent entity  If the answer is yes, the it is non-identifying. The core issue whether the primary key of the child includes the foreign key of the parent. In the non-identifying relationship the child's primary key (PK) cannot include the foreign key (FK). Ask yourself this question.

[PDF] CA ERwin Data Modeler Data Modeling Overview Guide, RI, Cardinality, and Non-Identifying Relationships. A data model is independent of implementation, so it does not require that the implementation is in any particular database conservation, it creates confusion with query logic. In fact, this  Identifying relationship - where the child entity's identity/existence relies solely on the parent entity. No parent entity means no child entity. For example, if you delete an order (order table), you probable want all the order's line items to disappear as well (order_line_item), because line items without an order are pointless.

Defining Identifying and Non-Identifying Relationships in Vertabelo, or non-identifying. We can define a relationship as identifying or non-​identifying in Vertabelo as well. This article will explain the way to do so. 006 Relationships Identifying and Non Identifying - YouTube. This channel will make you smarter This channel will make you smarter Skip navigation. Sign in. Search. Loading Close. This

Identifying vs. non identifying relationships: Why was I never taught , I saw there was an option for "identifying" and "non identifying" relationships. So, using this identifying relationship, I notice that if you want to query the tables, you don't need I feel like I'm confused as to when to enforce such a thing. I have been reading up on identifying and non-identifying relationships and am still a bit confused about the whole thing. I have been using MySQL Workbench to design the database. Now, the criterias for parents and children are that a Parent can have one or more Children and vice versa - ergo, the relationship between Parents and Children is a

Comments
  • 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 ?
  • so every weak entity has an identifying relationship?
  • Good answer, but could you please answer this question about why it even matters in ERD design? stackoverflow.com/questions/34846418/…
  • @Ochado, I don't answer questions on Stack Overflow any more.
  • :) I think I am winding myself up a bit by working with a logical model today when normally I don't. So many to many where a lookup table would lay in between kind of throws a wrench into it for me. Happy Wednesday. Thanks for sharing you knowledge.
  • So, if I read your response right, ER modeling is just a tool to help conceptualize the database (similar to how UML modeling is a tool used to conceptualize software systems). While each tool is helpful, that does come with caveats that they have their own syntax and problems that can add further confusion. I hadn't thought of this aspect. Thanks.
  • If ER means 'Entity-Relationship', what means D?
  • D is the family of all languages that adhere to the rules laid out in "Databases, Types & the Relational Model", and/or "The Third Manifesto".
  • The Third Manifesto, TTM for short, by Chris Date & Hugh Darwen, is their blueprint for what a data[base] processing language ought to look like in the 21st century. It defines the rules and requirements that said 21-st century language must abide with. One of those requirements is the ability to express/declare any database constraint what so ever in a formally precise way. Don't misunderstand "database constraint" to mean "only the types of constraint that 20th century SQL engines can support". No, "database constraint" really means "any constraint what so ever that governs the database.
  • That formally precise way of expressing "any database constraint what so ever" will come rather close, syntactically, to the database design specification language/syntax used in "Applied Mathematics for Database Professionals". It will (inevitably) look quite radically different from the constraint specification techniques of more traditional methods such as ERD and even of Halpin ORM (whose support for constraint specification is way more complete than ERD's).
  • +1 - Thanks for clearing this up! I (and another co-worker, also not familiar with database design) were struggling with this as we were not seeing why one or the other mattered as it achieved the same effect. This really helps.
  • To follow up on your answer, could you please answer or comment on this question about why it even matters in ERD design? stackoverflow.com/questions/34846418/…
  • But in the answer of @bill-karwin here he said that a non-identifying relationship can be optional or mandatory
  • +1 for "identifying relationships are useful for avoiding long join paths". It would be great if you elaborated more on this.