refers to maintaining the validity of foreign keys when the primary key in the parent table changes.

1. adding or updating records

image.png
Can we add a new credit card row without also adding a Customer row?

  • Need to assign new customer id or
  • Need to map to customer id PK already in the table or
  • Need to map customer ID to NULL in credit card table => may be new customer not yet identified

2. removing records

image.png
What happens if a merchant no longer does business with the company?

  • Remove a row from Merchants table (PK)
  • What about the Transactions associated with that deleted merchant (FK)?

If we left those records in the Transactions table, the Merchant_ID FK in the child table would not have a corresponding PK in the parent table

  • This violates Referential Integrity

3.Three most common options

  1. Restrict (a.k.a. “no action”)
  2. Cascade
  3. Set Null (or Set Default)

1.Restrict Option

image.png
i.e. you can’t delete or update a PK record in a parent table if there is a restrict constraint related to that record as an FK in a child table

2.Cascade Option

image.png
i.e. by setting up the cascade option the child table FK rows are automatically deleted when you delete the PK row in the parent table
image.png

3. Other referential actions

image.png

4. Referential Integrity in MySQL

image.png