Understanding Data Consistency in PostgreSQL

Up until now, we’ve learned:

• What primary keys are
• What foreign keys are
• How they help us create relationships between tables

Now it’s time to understand something very important:

🔒 Data Consistency

What is Data Consistency?

Data consistency means:

The database ensures that the data we insert actually makes sense.

In other words:

• If a photo says it belongs to a user…
• That user must actually exist.

The database protects us from inserting nonsense relationships.

The Three Insertion Scenarios

Whenever we insert a record that includes a foreign key, there are only three possible situations.

Let’s use our example:

photos
- id
- url
- user_id  (foreign key referencing users.id)

✅ Scenario 1: Referencing a User That Exists

Suppose we already have users with IDs:

1
2
3
4

Now we insert a photo like this:

INSERT INTO photos (url, user_id)
VALUES ('http://newphoto.jpg', 1);

Here:

• user_id = 1
• User with ID 1 exists

✔ Result:

The insertion works perfectly.

This is valid data.

The relationship is correct.

❌ Scenario 2: Referencing a User That Does NOT Exist

Now imagine we try this:

INSERT INTO photos (url, user_id)
VALUES ('http://brokenphoto.jpg', 9999);