What s the difference between identifying and non-identifying relationships

Question

I haven t been able to fully grasp the differences  Can you describe both concepts and use real world examples

User · Answer

If you consider that the child item should be deleted when the parent is deleted, then it is an identifying relationship.

If the child item should be kept even though the parent is deleted, then it is a non-identifying relatio?ship.

As an example, I have a training database with trainees, trainings, diplomas and training sessions :

trainees have an identifying relationship with training sessions
trainings have an identifying relationship with training sessions
but trainees have a non-identifying relationship with diplomas

Only training sessions should be deleted if one of the related trainee, training or diploma is deleted.

User · Answer

Here s a good description   Relationships between two entities may be classified as being either  identifying  or  non-identifying   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  In addition  non-identifying relationships may be further classified as being either  mandatory  or  non-mandatory   A mandatory non-identifying relationship exists when the value in the child table cannot be null  On the other hand  a non-mandatory non-identifying relationship exists when the value in the child table can be null   http   www sqlteam com article database-design-and-modeling-fundamentals  Here s a simple example of an identifying relationship   Parent ------ ID  PK  Name  Child ----- ID  PK  ParentID  PK  FK to Parent ID  -- notice PK Name   Here s a corresponding non-identifying relationship   Parent ------ ID  PK  Name  Child ----- ID  PK  ParentID  FK to Parent ID  -- notice no PK Name

User · Answer

Like well explained in the link below  an identifying relation is somewhat like a weak entity type relation to its parent in the ER conceptual model  UML style CADs for data modeling do not use ER symbols or concepts  and the kind of relations are  identifying  non-identifying and non-specific   Identifying ones are relations parent child where the child is kind of a weak entity  even at the traditional ER model its called identifying relationship   which does not have a real primary key by its own attributes and therefore cannot be identified uniquely by its own  Every access to the child table  on the physical model  will be dependent  inclusive semantically  on the parent s primary key  which turns into part or total of the child s primary key  also being a foreign key   generally resulting in a composite key on the child side  The eventual existing keys of the child itself are only pseudo or partial-keys  not sufficient to identify any instance of that type of Entity or Entity Set  without the parent s PK   Non-identifying relationship are the ordinary relations  partial or total   of completely independent entity sets  whose instances do not depend on each others  primary keys to be uniquely identified  although they might need foreign keys for partial or total relationships  but not as the primary key of the child  The child has its own primary key  The parent idem  Both independently  Depending on the cardinality of the relationship  the PK of one goes as a FK to the other  N side   and if partial  can be null  if total  must be not null  But  at a relationship like this  the FK will never be also the PK of the child  as when an identifying relationship is the case   http   docwiki embarcadero com ERStudioDA XE7 en Creating and Editing Relationships

User · Answer

Do attributes migrated from parent to child help identify1 the child    If yes  the identification-dependence exists  the relationship is identifying and the child entity is  weak   If not  the identification-dependence doesn t exists  the relationship is non-identifying and the child entity  strong     Note that identification-dependence implies existence-dependence  but not the other way around  Every non-NULL FK means a child cannot exist without parent  but that alone doesn t make the relationship identifying   For more on this  and some examples   take a look at the  Identifying Relationships  section of the ERwin Methods Guide   P S  I realize I m  extremely  late to the party  but I feel other answers are either not entirely accurate  defining it in terms of existence-dependence instead of identification-dependence   or somewhat meandering  Hopefully this answer provides more clarity       1 The child s FK is a part of child s PRIMARY KEY or  non-NULL  UNIQUE constraint

User · Answer

Non-identifying relationship  A non-identifying relationship means that a child is related to parent but it can be identified by its own   PERSON    ACCOUNT                   pk id     pk id  name      fk person id            balance   The relationship between ACCOUNT and PERSON is non-identifying   Identifying relationship  An identifying relationship means that the parent is needed to give identity to child  The child solely exists because of parent   This means that foreign key is a primary key too   ITEM      LANGUAGE    ITEM LANG                                 pk id     pk id       pk fk item id   name      name        pk fk lang id                         name   The relationship between ITEM LANG and ITEM is identifying  And between ITEM LANG and LANGUAGE too

User · Answer

There is another explanation from the real world   A book belongs to an owner  and an owner can own multiple books  But  the book can exist also without the owner  and ownership of it can change from one owner to another  The relationship between a book and an owner is a non-identifying relationship   A book  however  is written by an author  and the author could have written multiple books  But  the book needs to be written by an author - it cannot exist without an author  Therefore  the relationship between the book and the author is an identifying relationship

User · Answer

An identifying relationship is between two strong entities  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   For example   a relationship between a seller and a book where a book is being sold by a seller may exist where seller may have its own primary key but its entity is created only when a book is being sold  Reference based on Bill Karwin

User · Answer

Bill s answer is correct  but it is shocking to see that among all the other answers no one points out the most significant aspect  It has been said over and over again  that in an identifying relationship the child can not exist without the parent   e g  user287724   This is true  but completely misses the point  It would be enough for the foreign key to be non-null to achieve this  It does not need to be part of the primary key  So here is the real reason  The purpose of an identifying relationship is that the foreign key can NEVER CHANGE  because it is part of the primary key    therefore identifying

User · Answer

A complement to Daniel Dinnyes  answer  On a non-identifying relationship  you can t have the same Primary Key column  let s say   quot ID quot   twice with the same value  However  with an identifyinig relationship  you can have the same value show up twice for the  quot ID quot  column  as long as it has a different  quot otherColumn ID quot  Foreign Key value  because the primary key is the combination of both columns  Note that it doesn t matter if the FK is  quot non-null quot  or not   -

User · Answer

user287724 s answer gives the following example of the book and author relationship      A book however is written by an author  and the author could have written multiple books  But the book needs to be written by an author it cannot exist without an author  Therefore the relationship between the book and the author is an identifying relationship    This is a very confusing example and is definitely not a valid example for an identifying relationship   Yes  a book can not be written without at least one author  but the author it s foreign key  of the book is NOT IDENTIFYING the book in the books table   You can remove the author  FK  from the book row and still can identify the book row by some other field  ISBN  ID     etc    BUT NOT the author of the book     I think a valid example of an identifying relationship would be the relationship between  products table  and a  specific product details table  1 1  products table  ------ --------------- ------- --------   id PK  Name            type    amount     ------ --------------- ------- --------   0      hp-laser-510    printer 1000       ------ --------------- ------- --------   1      viewsonic-10    screen  900        ------ --------------- ------- --------   2      canon-laser-100 printer 200        ------ --------------- ------- --------   printers details table  -------------- ------------ --------- --------- ------   Product ID FK  manufacturer cartridge color     papers   -------------- ------------ --------- --------- ------   0              hp           CE210     BLACK     300      -------------- ------------ --------- --------- ------   2              canon        MKJ5      COLOR     900      -------------- ------------ --------- --------- ------    please note this is not real data   In this example the Product ID in the printers details table is considered a FK references the products id table and ALSO a PK in the printers details table   this is an identifying relationship because the Product ID FK  in the printers table IS IDENTIFYING the row inside the child table   we can t remove the product id from the child table because we can t identify the row any more because we lost it s primary key  If you want to put it in 2 lines      an identifying relationship is the relationship when the FK in the   child table is considered a PK or identifier  in the child table while   still references the parent table   Another example may be when you have 3 tables  imports - products - countries  in an imports and exports for some country database  The import table is the child that has these fields the product id FK   the country id FK    the amount of the imports   the price   the units imported   the way of transport air  sea    we may use the  product id  thecountry id   to identify each row of the imports  if they all in the same year  here the both columns can compose together a primary key in the child table imports  and also referencing there parent tables   Please I m happy I finally understand the concept of the identifying relationship and non identifying relationship  so please don t tell me I m wrong with all of these vote ups for a completely invalid example  Yes logically a book can t be written without an author but a book can be identified without the author In fact it can t be identified with the author   You can 100  remove the author from the book row and still can identify the book

User · Answer

Let s say we have those tables   user -------- id name   comments ------------ comment id user id text   relationship between those two tables will identifiying relationship  Because   comments only can be belong to its owner  not other users  for example  Each user has own comment  and when user is deleted  this user s comments also should be deleted

User · Answer

A good example comes from order processing   An order from a customer typically has an Order Number that identifies the order  some data that occurs once per order such as the order date and the Customer ID   and a series of line items   Each line item contains an item number that identifies a line item within an order  a product ordered  the quantity of that product   the price of the product  and the amount for the line item  which could be computed by multiplying the quantity by the price   The number that identifies a line item only identifies it in the context of a single order   The first line item in every order is item number  1    The complete identity of a line item is the item number together with the order number of which it is a part    The parent child relationship between orders and line items is therefore an identifying relationship  A closely related concept in ER modeling goes by the name  subentity    where line item is a subentity of order   Typically  a subentity has a mandatory child-parent identitying relationship to the entity that it s subordinate to   In classical database design   the primary key of the LineItems table would be  OrderNumber  ItemNumber    Some of today s designers would give an item a separate ItemID  that serves as a primary key  and is autoincremented by the DBMS   I recommend classical design in this case

User · Answer

The identifing relaionship means the child entity is totally depend on the existance of the parent entity  Example account table person table and personaccount The person account table is identified by the existance of account and person table only   The non identifing relationship means the child table does not identified by the existance of the parent table example there is table as accounttype and account accounttype table is not identified with the existance of account table

User · Answer

An Identifying relationship specifies that a child object cannot exist without the parent object  Non-identifying relationships specifies a regular association between objects  1 1 or 1 n cardinality   Non-identifying relationships can be specified as optional where a parent is not required or mandatory where a parent is required by setting the parent table cardinality

User · Answer

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     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 person id in the primary key of PhoneNumbers   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 of States state   Person is a child table with respect to States   But a row in Person is not identified by its state attribute   I e  state is not part of the primary key of Person   A non-identifying relationship can be optional or mandatory  which means the foreign key column allows NULL or disallows NULL  respectively      See also my answer to Still Confused About Identifying vs  Non-Identifying Relationships

[database] What's the difference between identifying and non-identifying relationships?

Examples related to database

Examples related to database-design

Examples related to data-modeling

Examples related to identifying-relationship