Database Design: How Foreign Keys Help Enforce Data Integrity

As I was studying SQL over the Holy Week, one of the questions that stumped me and led me to wormhole through the internet was this:

I followed the course's instruction but was a bit confused with why this was a good thing and how this is exactly how foreign keys reinforce data integrity.

At first, I was confused because the SQL code seems correct: (1) it follows the correct syntax and (2) the INSERT INTO clause should allow the analyst to add rows.

However, we encountered an error.

FOREIGN KEY constraint failed        

Let's explore together how foreign keys enforce data integrity through this example. Here, we get to unpack how good database design help prevent invalid data from being stored.

Point 1: Understanding the Hidden Database Design

The original code did not show the table definitions, which made the situation confusing. However, behind the scenes, the database was likely structured like this:

/* Plausible Documentation for the users and videos table*/

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT
);

CREATE TABLE videos (
    video_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    title TEXT,
    category TEXT,

    FOREIGN KEY (user_id)
    REFERENCES users(id)
);        

In this particular line:

FOREIGN KEY (user_id)
REFERENCES users(id)        

It basically tells the database: Every user_id in videos must match an existing id in users.

Which creates a parent-child relationship: users (parent) --> videos (child)

Parent Table vs Child Table

Parent table: the independent table that contains the primary key.

Child table: the table that depends on the parent and stores the foreign key.

Why only the child table needs the foreign key and why is it the videos table?

This is because each video belongs to a user, so it needs to store user_id to point back to the parent. But a user can exist in a database even without any video info.

How to know which table should be the parent and child?

I just follow these simple questions:

“Who depends on who” → The dependent table gets the foreign key.

"Should this table exist independently?" → If yes, that is the parent table. No foreign key needed

"Does this table need to reference another table?" → If yes, that is the child table. Add a foreign key.

Point 2: What Happened During the Failed Insert

Reminder: the context is that there is no user with and ID of 298 in the users table.

INSERT INTO videos (video_id, user_id, title, category)
VALUES (109, 298, 'Unicorns are Real', 'company memes');        

How the SQL code ran:

1. The database checks if user_id = 298 exist in the users table?
2. After checking, it finds out that the user_id = 298 does not exist.
3. Return an error message:

FOREIGN KEY constraint failed        

The database blocked the insert not because INSERT INTO is wrong. But because the relationship rule was violated.

Rule: each video belongs to a user.

Since user_id = 298 does not exist in the users table, you CANNOT add that to the videos table.

Point 3: How can we make this insert succeed?

/* Let's create the users and videos table first */

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT
);

CREATE TABLE videos (
    video_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    title TEXT,
    category TEXT,

    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

/* To make the insert succeed, the parent record must exist first */

INSERT INTO users (id, username)
VALUES (298, 'NewUser');

/* Code from the question */

INSERT INTO videos (video_id, user_id, title, category)
VALUES (109, 298, 'Unicorns are Real', 'company memes'
);        

Now the insert will work! No Error Message.

To conclude, the error message

FOREIGN KEY constraint failed        

was proof that Foreign Keys enforce referential integrity. It prevents invalid relationships from entering the database and ensures that stored data remains reliable and meaningful.

Without Foreign Keys, an orphan record could have been created. (A child row references a non-existent parent row).