SQL Database Design » 13. Design Heuristics » 13.2 Dependent Keys

13.2 Dependent Keys

The dependency of keys is usually referred to by the terms identifying and non-identifying relationships. To be precise, identifying and non-identifying relationships are logical-level terms, because a "relationship" is rather a conceptual-level term. Identifying and non-identifying relationships should rather be called identifying and non-identifying foreign keys. The latter simplifies their comprehension, but I decided to stick to the more common terms. Just remember, that the concept of dependent keys relates to foreign keys on the logical and physical levels and not relationships on the conceptual level.

Using non-identifying relationships, the related rows of two interconnected tables can exist independently of each other. If the two rows rely upon each other, this an identifying relationship. Such a row, which cannot be identified by its own attributes alone, is also called a weak entity type. Identifying relationships implement tight coupling between entities, whereas non-identifying relationships implement loose coupling. This is why design tools usually use solid lines for identifying and dashed lines to represent non-identifying relationships.

An identifying relationship is not only a foreign key in this table, but also a (full part of the) primary key. Identifying relationships can be summarized as "primary-key-foreign-key overlays". If any of a table's foreign key columns is no longer part of the primary key, the identifying relationship becomes non-identifying automatically.

As a consequence, identifying relationships are foreign keys that cannot become NULL (because the foreign key columns are part of the table's primary key). On the other hand, that does not mean, non-identifying relationships may always become NULL. Non-identifying relationships may or may not become NULL according to their NOT NULL constraints. The "nullability" property is only an implicit property and it is not suitable for distinguishing identifying from non-identifying relationships (which some people insist on to be correct).

But what do identifying and non-identifying relationships give us? There are three use cases that regularly appear:

  1. Single inheritance relationships
  2. Many-to-many relationships
  3. One-to-one and one-to-many compositions

The first use case are single inheritance relationships. The usual approach here is to define a foreign key that references to the super table's primary key, which is itself a primary key in the referencing table as well. The second use case are regular many-to-many relationships, also known as "join tables". You simply do the same as above for the two interconnected tables and use the foreign keys as primary keys, too. There is usually no need to introduce a separate ID for join tables. When doing so, the two foreign keys become non-identifying relationships. The third use case are compositions, where the parts share the parent's primary key. Compositions with identifying relationship add another column to the primary key. Because the added columns are not part of the foreign key, the relationship is still identifying.

If you have never heard of identifying and non-identifying relationships before, many modeling tools, such as MySQL Workbench, make use of that notion. I recommend you to install such a tool to observe the effects of changing the primary and foreign constraints of identifying and non-identifying relationships.

References for Dependent Keys:

Last updated: 2010-08-04