Concepts / Foreign Keys and Referential Integrity

Foreign Keys and Referential Integrity

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.

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

In the real world

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.

Customer 1one instanceOrder 1many-side instanceOrder 2many-side instanceOrder 3many-side instanceconnects toconnects toconnects to
How can one instance of one entity be connected to multiple instances of another entity?
Key point

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.

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

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.

Customerprimary keyOrder 1foreign key referencesCustomerOrder 2foreign key referencesCustomerOrder 3foreign key referencesCustomerreferenced byreferenced byreferenced by
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?
Worked example

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.

  1. 1
    Identify the one side

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

  2. 2
    Place the primary key

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

  3. 3
    Identify the many side

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

  4. 4
    Place the foreign key

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

Result

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

Checking Referential Integrity

Definition

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.

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

Common Key-Placement Mistakes

  • Common mistake

    Putting the foreign key on the one side

    What it looks like

    Generated example: placing the Order reference in Customer instead of placing the Customer key value in Order.

    Why it fails

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

    Do this instead

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

  • Common mistake

    Putting the primary key on the many side

    What it looks like

    Generated example: treating the Order entity as the location of the primary key for the relationship simply because it contains multiple records.

    Why it fails

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

    Do this instead

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

  • Common mistake

    Ignoring the notation symbols

    What it looks like

    Generated example: assuming the entity drawn on the left is always the one side.

    Why it fails

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

    Do this instead

    Read the endpoint symbols before deciding where either key belongs.

  • Common mistake

    Assuming any foreign-key value is valid

    What it looks like

    Generated example: storing a foreign-key value of 999 when no referenced primary key has that value.

    Why it fails

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

    Do this instead

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

Practice Solution

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

  1. 1
    Place the primary key

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

  2. 2
    Place the foreign key

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

  3. 3
    Check the reference

    Each foreign-key value in Book must refer to an existing primary-key value in Author.

Result

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

At a glance

  • 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

Related concepts

More concepts starting with F