Relationships and Integrity

Why Stories Need Joins

A customer places many orders; an order has many lines; each line points at one product. These are one-to-many links that relate tables instead of repeating data.

Creating a Relationship

Database Tools → Relationships → Show Table, drag the CustomerID from Customers onto CustomerID in Orders:

Customers (1) ---< Orders (many)
   CustomerID          CustomerID

• The one side fields are the primary key.
• The many side fields are the foreign key.

Referential Integrity + Cascade

Check Enforce Referential Integrity - Access refuses an order for a customer that does not exist:

• Cascade Update - change the customer ID once; orders follow automatically.
• Cascade Delete - delete the customer and their orders go too. Use with care!

Relationship Types

• One-to-Many - the default and the workhorse.
• One-to-One - rare; splitting a wide table.
• Many-to-Many - via a junction table (e.g. Students <-> Courses via Enrollment).

Key Points

• Relationships map your nouns (customer, order, product) to tables.
• Enforce integrity to stop orphan records at the source.
• Use cascade delete only when the children are truly owned by the parent.