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

• 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

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.

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

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

Do attributes migrated from parent to child help identify¹ 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...

¹ The child's FK is a part of child's PRIMARY KEY or (non-NULL) UNIQUE constraint.

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.

A complement to Daniel Dinnyes' answer:

On a non-identifying relationship, you can't have the same Primary Key column (let's say, "ID") twice with the same value.

However, with an identifyinig relationship, you can have the same value show up twice for the "ID" column, as long as it has a different "otherColumn_ID" Foreign Key value, because the primary key is the combination of both columns.

Note that it doesn't matter if the FK is "non-null" or not! ;-)