When “Valid Data” Turned Out to Be a Lie

We had just finished a clean release.

Validation logic? Solid.
API layer? Strict.
Frontend checks? Painfully detailed.

We were proud of it.

Then support escalated a strange bug: two users had the same phone number. Another user had an age of -3. And a bulk import script had quietly inserted duplicate identifiers into the system.

Nothing crashed.

Nothing threw a dramatic exception.

The database simply… accepted everything.

That’s when I realized something uncomfortable:

We were trusting our application code to behave forever.

And applications don’t behave forever.

In this guide, we are going to explore 3 key concepts to help you master data integrity in MySQL:

1. UNIQUE constraints

2. CHECK constraints

3. Named constraints

This isn’t about memorizing SQL syntax.
This is about designing systems that defend themselves.

---

1️⃣ Constraints: The Database as a Security Gate

A constraint is a rule enforced by the database that ensures data validity.

At first glance, this sounds basic. Almost boring.

But here’s the real-world truth:

• Backend validation can fail.

• APIs can be bypassed.

• Multiple services can write to the same database.

• Internal scripts don’t always follow your validation rules.

In production systems, data corruption doesn’t happen because someone is evil.

It happens because someone forgot something.

The Factory Analogy

Think of your database as a factory.

Your application is just a delivery truck dropping off products.

Constraints are the security gates inside the factory:

• UNIQUE → “No duplicate products allowed.”

• CHECK → “Every product must pass inspection.”

• Named constraints → “Every inspection station is labeled.”

Even if the delivery truck makes a mistake, the factory refuses bad goods.

That’s the level of protection serious systems rely on.

---

2️⃣ UNIQUE Constraint — Your First Line of Defense

What It Does

A UNIQUE constraint ensures that values in a column cannot be duplicated.

Simple.

But dangerously underestimated.

---

Real-World Scenario

Let’s say you're building a contact management system.

Business requirement:

No two users can share the same phone number.

You could enforce this in backend code.

And you should.

But if you stop there, you're gambling.

Here’s the SQL:

CREATE TABLE contacts (
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(15) NOT NULL UNIQUE
);

Now try inserting duplicates:

INSERT INTO contacts (name, phone)
VALUES ('billybob', '8781213455');

INSERT INTO contacts (name, phone)
VALUES ('billybob', '8781213455');

Second insert fails with:

Duplicate entry '8781213455' for key 'phone'

The database says no.

And that “no” is what saves your system.

---

Why It Matters in Production

Imagine:

• A background job retries failed inserts.

• Two microservices write simultaneously.

• A data migration script runs twice.

Without UNIQUE, your data slowly rots.

You don’t notice at first.

Then analytics breaks.
Then authentication logic breaks.
Then trust breaks.

---

What Happens If It Fails?

If you forget UNIQUE:

• Login systems can map multiple users to one email.

• Payment systems can duplicate transactions.

• Notifications can send to the wrong person.

These bugs are subtle. Expensive. Embarrassing.

---

Senior Dev Perspective

Here’s something important:

A UNIQUE constraint automatically creates an index.

That means:

• Faster lookups.

• Faster equality checks.

• Slightly heavier write cost.

So if you're designing a read-heavy system (like a user lookup service), UNIQUE gives you integrity and performance.

But if you're in a high-write environment, measure carefully.

Balance is architecture.

---

3️⃣ CHECK Constraint — Enforcing Business Logic at the Source

What It Does

A CHECK constraint enforces a logical rule on column values.

Example requirement:

Age must be positive.

Here’s the SQL:

CREATE TABLE users (
  username VARCHAR(20) NOT NULL,
  age INT CHECK (age > 0)
);

Now negative ages are rejected automatically.

---

Why This Is More Powerful Than It Looks

We’ve all written backend validations like:

if (age <= 0) throw new Error("Invalid age");

Feels safe.

Until someone:

• Disables validation in a script.

• Writes directly to DB in a migration.

• Connects via admin tool.

• Uses an older service version.

Your database doesn’t know your intentions.

Unless you tell it.

---

Advanced Example: Palindrome Table

CREATE TABLE palindromes (
  word VARCHAR(100) CHECK(REVERSE(word) = word)
);

Only words that read the same backward are allowed.

Is this practical? Not really.

Is it powerful? Absolutely.

This demonstrates:

• Database-level validation logic

• Complex rule enforcement

• Deterministic guarantees

---

What Happens If CHECK Is Missing?

Let’s say you forget it.

• Negative inventory slips in.

• Prices become zero or negative.

• Percentages exceed 100.

Now your dashboards lie.

Your reports lie.

Your decisions become wrong.

And the worst part?

The system keeps running.

Corrupted data doesn’t crash loudly.
It silently poisons analytics.

---

Important MySQL Reality Check

Older MySQL versions ignored CHECK constraints.

Let that sink in.

Developers thought they were protected.

They weren’t.

So always verify your version supports CHECK enforcement.

Never assume.

---

Senior Dev Perspective

CHECK constraints are like guardrails on mountain roads.

You may never hit them.

But when you do, you’ll be grateful they exist.

---

4️⃣ Named Constraints — Debugging Like a Professional

What It Is

Named constraints allow you to explicitly label a constraint.

Example:

CREATE TABLE users2 (
  username VARCHAR(20) NOT NULL,
  age INT,
  CONSTRAINT age_not_negative CHECK (age >= 0)
);

Another example:

CREATE TABLE palindromes2 (
  word VARCHAR(100),
  CONSTRAINT word_is_palindrome CHECK(REVERSE(word) = word)
);

---

Why This Matters in Large Systems

In production, generic error messages are useless.

Without names, you get:

Check constraint violated

With names, you get:

Check constraint 'age_not_negative' is violated

Now logs become readable.

Monitoring becomes clearer.

Schema migrations become manageable.

---

What Happens If You Don’t Name Them?

You spend 20 minutes searching migration files to figure out which constraint failed.

Ask me how I know.

Named constraints reduce debugging time.

Time is money.

Sleep is sanity.

---

5️⃣ DevOps & SRE Angle — Where Constraints Shine

In production environments:

Constraints prevent corrupted data during:

• Bulk imports

• Script failures

• Microservice miscommunication

• Partial deployments

Constraint violations surface in logs.

They become observable events.

That means:

• Alerts trigger.

• Metrics reflect reality.

• Incidents are contained early.

Professional systems rely on:

Database-enforced integrity, not developer discipline.

Discipline is great.

But discipline under pressure? That’s where bugs win.

---

6️⃣ Performance Trade-Offs (The Honest Conversation)

Constraints aren’t free.

• UNIQUE → creates indexes.

• More indexes → slower writes.

• Heavy CHECK logic → adds evaluation cost.

So you balance:

• Read-heavy systems → more indexing.

• Write-heavy systems → measure and optimize.

But here’s the thing:

Cleaning corrupted data later is far more expensive than slightly slower inserts today.

I’ve done both.

Choose wisely.

---

7️⃣ Mental Model to Lock In

Constraints are:

• Guardrails

• Self-defense mechanisms

• Automatic validators

• Data integrity contracts

They make your system:

• Predictable

• Safe

• Scalable

Without constraints, data corruption isn’t “possible.”

It’s inevitable.

---

The Bridge Back

Remember the duplicate phone numbers?

We fixed it in minutes once we added a UNIQUE constraint.

We added CHECK constraints for business rules.

We named everything.

The system stopped trusting us.

And started protecting itself.

That’s the shift.

The moment you stop writing SQL for functionality
and start designing databases for resilience.

You’re no longer just storing data.

You’re defining contracts.

And if you’ve ever debugged production data corruption, you know:

There’s nothing more peaceful than a database that says, “No.”

---