You've seen foreign keys before—now, let's focus on how they connect tables and enforce relationships through constraints. In this lesson, we'll dive deeper and focus on the raw SQL behind building table relationships and controlling what happens when data changes.

Foreign keys are more than just references; they're powerful constraints that keep your data consistent across tables.

Engagement Message

Why is it important to use foreign keys instead of just storing related IDs?

The basic syntax for creating a foreign key looks like this:

This means that every value in column_name must match a value in parent_column of the parent_table. For example, in our scenario:

Engagement Message

Which table is the parent, and which is the child in this relationship?

You add a foreign key constraint to a table as part of the CREATE TABLE statement. Here's how you would ensure every order has a valid customer:

Engagement Message

What would happen if you tried to insert an order with a customer_id that doesn't exist in the customers table?

Foreign keys stop you from breaking relationships between tables. For example, you can't add an order with a customer_id that isn't in the customers table, or remove a customer if there are orders linked to them.

But what should happen to orders if a customer is deleted or their ID is changed? SQL lets you set rules for this to protect your data's consistency.

Engagement Message

Imagine you try to delete a customer who still has active orders in the system. What should the database do in this situation, and why?