Concepts / Foreign Keys and Referential Integrity

Foreign Keys and Referential Integrity

A one-to-many relationship connects one entity instance to multiple instances of another entity.

  • Programming

One Record, Many Connections

A one-to-many relationship describes a connection in which one instance of an entity is associated with multiple instances of another entity. The relationship becomes useful when the database must connect related information without losing track of which records belong together.

Generated example: imagine a Customer entity and an Order entity. One customer can be connected to multiple orders. The customer is the one side of this relationship, while the orders are the many side. This example illustrates the relationship pattern; the source pack does not provide these entity names.

Following One Instance

Start with one instance on the one side. That single instance may connect to several instances on the many side. In the generated Customer and Order example, one customer record can be linked to multiple order records. The important direction is not that every record has the same number of connections; it is that the relationship is organized around one instance on one side and multiple instances on the other.

connects toconnects toconnects toCustomer 1one instanceOrder 1many-side instanceOrder 2many-side instanceOrder 3many-side instance
How can one instance of one entity be connected to multiple instances of another entity?

The many side contains multiple related instances, but each connection still points back to the one side. This is the relationship pattern that determines where the keys belong.

Reading Crow's Foot Symbols

Crow's Foot notation shows relationship cardinality visually. A simple vertical line represents the one end. A crow's foot, which looks like three lines spreading outward, represents the many end. When reading a relationship, locate these symbols first: the vertical line identifies the entity on the one side, and the crow's foot identifies the entity on the many side.

one endrelationship linemany endCustomerentityvertical lineonecrow's footmanyOrderentity
How do the symbols at each end of the relationship show which entity has one instance and which entity can have many instances?

Do not decide which table receives the foreign key by looking at the physical order of tables in a diagram. Read the symbols instead. The vertical line marks the one side, and the crow's foot marks the many side.

Placing the Two Keys

The primary key is placed on the one side of a one-to-many relationship. It uniquely identifies each instance of that entity. The foreign key is placed on the many side. It stores the primary key value from the one side, creating the link back to the referenced entity.

referenced byreferenced byreferenced byCustomerprimary keyOrder 1foreign key referencesCustomerOrder 2foreign key referencesCustomerOrder 3foreign key referencesCustomer
Where is the primary key stored, where is the foreign key stored, and how does the foreign key connect many child records to one parent record?

Mapping Keys in a One-to-Many Relationship

Generated example: A Customer entity is on the one side of a relationship with an Order entity on the many side. Determine where the primary key and foreign key belong.

Identify the one side: The Customer entity is the one side in this generated relationship.

Place the primary key: The primary key belongs in the Customer entity because the primary key is placed on the one side.

Identify the many side: The Order entity is the many side because one customer can be connected to multiple orders in this example.

Place the foreign key: The foreign key belongs in the Order entity and stores the primary key value from Customer.

The Customer entity contains the primary key, and the Order entity contains the foreign key that references it.

Checking Referential Integrity

Referential integrity is the guarantee that every foreign key value actually refers to an existing primary key value in the referenced table.

The foreign key creates a dependable link because its value is expected to match a primary key value on the one side. If a foreign key value does not match an existing primary key value, that value fails the referential-integrity guarantee. The related information cannot be reliably connected through that value, creating a data inconsistency.

matching referenceno matching primary keyCustomer 101primary key 101Orderforeign key 101Customer 101primary key 101Orderforeign key 999
What changes when a foreign-key value does not match an existing primary-key value in the referenced table?

Common Key-Placement Mistakes

  • Putting the foreign key on the one side

    The foreign key belongs on the many side and references the primary key on the one side.

    Fix: Identify the crow's foot first, then place the foreign key in that entity.

  • Putting the primary key on the many side

    The primary key is placed on the one side of the relationship.

    Fix: Place the relationship's primary key on the entity marked with the simple vertical line.

  • Ignoring the notation symbols

    The one and many ends are identified by the vertical line and crow's foot symbols, not by left-right position.

    Fix: Read the endpoint symbols before deciding where either key belongs.

  • Assuming any foreign-key value is valid

    Referential integrity requires every foreign key value to refer to an existing primary key value.

    Fix: Check that the foreign-key value has a matching primary-key value on the one side.

Practice the Relationship

EASY

Generated practice: An Author entity is on the one side of a relationship, and a Book entity is on the many side. State which entity receives the primary key and which entity receives the foreign key. Then explain what the foreign-key value must refer to for referential integrity to hold.

Hints
  • Use the relationship ends rather than the entity names to locate the keys.
  • The primary key is placed on the one side.
  • The foreign key is placed on the many side and must refer to an existing primary key value on the one side.

Practice Solution

Generated practice: An Author entity is on the one side, and a Book entity is on the many side.

Place the primary key: The primary key belongs in Author because Author is on the one side.

Place the foreign key: The foreign key belongs in Book because Book is on the many side.

Check the reference: Each foreign-key value in Book must refer to an existing primary-key value in Author.

Author contains the primary key, Book contains the foreign key, and each foreign-key value must identify an existing Author primary-key value.

Key Takeaways

  1. A one-to-many relationship connects one instance of one entity to multiple instances of another entity.
  2. A vertical line in Crow's Foot notation marks the one end, while the crow's foot marks the many end.
  3. The primary key is placed on the one side of the relationship.
  4. The foreign key is placed on the many side and references the primary key on the one side.
  5. Referential integrity requires every foreign-key value to refer to an existing primary-key value.

Key Takeaways

  • One-to-many relationships connect one entity instance to multiple instances of another entity.
  • Crow's Foot notation identifies the one end with a vertical line and the many end with a crow's foot.
  • The primary key belongs on the one side, while the foreign key belongs on the many side.
  • Referential integrity means that every foreign-key value refers to an existing primary-key value.

Related Topics

  • Primary Keys
  • Entity-Relationship Diagrams
  • Database Design
  • Data Integrity