Database constraints: unique and not null

1. Database constraints: role and meaning

If you used to look at database constraints as “something unpleasant that gets in the way of saving an object,” then today we will try to change the angle. A database is not a nag; it is your last, strictest guard. Code can make mistakes, validation can be forgotten, a test can miss a case, but the database must be able to say: “No, that is not allowed” — and stop the write.

Let’s make an important point explicit: validation and database constraints are not mutually exclusive things; they are two different layers of protection. In the web layer you validate the request DTO so you can quickly and neatly return 400 with a clear ApiProblem. But even if someone bypasses the web layer (an internal service, a migration script, an admin utility, a future batch process), the database is still obligated to protect invariants.

In ContentHub this is especially visible on slug. slug is the public article identifier in the URL. If you save the same string twice (for example, java-basics), the client suddenly gets a “fun lottery”: which article opens at /api/public/articles/java-basics? The correct answer is: none of them, because such a state should not be allowed at all. And this is where the unique constraint is not a “feature” but oxygen.

With not null it is even simpler: if an article title suddenly becomes null, then at best your API will start returning broken responses, and at worst you will hit an NPE at some unexpected moment (and, as is well known, NPE appears at night when you are already asleep and calls you on Slack).

To keep the two layers separate, it is useful to keep a short table in mind:

Now let us use the familiar mechanism from the other side. Before, flush() helped separate the object in memory from the row in the table, and here it is needed so that the database finally states its strict opinion about the data. If a constraint is supposed to fire, the test must be brought to the point of actual write.

2. Constraint violations and flush()

Database constraints are checked when the database actually sees the write. And here is the tricky part: in JPA you can live for a long time in the state of “my object has already been saved,” while in reality it is just sitting in the persistence context, like a draft email in the “Outbox” folder.

In @DataJpaTest this is especially important for two reasons. First, the test usually runs in a transaction, and the transaction is rolled back by default at the end. Second, without an explicit flush(), you may never force Hibernate to send SQL to the database at the moment when it is needed for verification. And then you get the classic situation: the test is green, but in the real application the commit would have blown up.

flush() is your JPA command: “Stop being clever and negotiating with the database later. Write the changes right now.” And it is exactly at that moment that the database switches on “checking rules” mode. That is why in constraint tests we almost always build the scenario so that the error is caught exactly on flush() — that is both honest and readable.

It is helpful to imagine this as a short flow:

flowchart TD
    A["persist(entity) / save(entity)"] --> B["Changes in the persistence context"]
    B --> C["flush()"]
    C --> D["SQL goes to the DB: INSERT/UPDATE"]
    D --> E{"DB checks constraints"}
    E -->|OK| F["Write successful"]
    E -->|Violation| G["Exception (on flush)"]

Notice that there is no “persist() threw an error” stage here. persist() most often just attaches the object to the context. The database can “punish” you only when you actually try to write the data.

And that is the main practical rule of the lecture: if you want to test database constraints with a test, you need flush() as the trigger.

3. unique constraint: uniqueness of Article.slug

When we start testing unique, it is important not to turn the test into a riddle: “why did it fail exactly here?” So we will act as step by step as possible. First we will create a valid article and write it to the DB (with flush()), and then we will try to write a second article with the same slug and call flush() again — that is where we expect the failure.

A valid article for a unique test

Right now we are not testing “title is required” or “category exists.” We are testing only the uniqueness of slug, which means all the other fields must be correct. It is like in a good experiment: change one factor, keep everything else stable. Otherwise you end up with a test that is supposedly about unique, but fails because of not null — and then you spend half an hour arguing with the monitor over which one of you is guilty.

A small helper for creating an article draft might look roughly like this:

import java.time.Instant;

private Article draftArticle(String slug, Category category) {
    // Build a valid article: in constraint tests it is important to break only one field at a time.
    Article a = new Article();

    // Required fields
    a.setTitle("Java Basics");
    a.setSlug(slug);

    // Fill the rest correctly so they do not "pull" the failure onto themselves
    a.setSummary("Short intro");
    a.setBody("""
            Longer text...
            """);
    a.setAuthorUsername("alice");
    a.setStatus(ArticleStatus.DRAFT);

    // Date is fixed so the test is repeatable
    a.setCreatedAt(Instant.parse("2026-01-01T10:00:00Z"));

    // The relation must also be valid, otherwise you can fail on FK / not-null by category
    a.setCategory(category);
    return a;
}

In data tests, it is useful to stick to one fixture shape: a valid Article plus a clearly prepared Category. Small helpers like draftArticle(...) are just shorthand for the same setup, not new magic.

Yes, it is a few more lines than one would like, but at least the test itself will make it clear: “we created a valid article, and the only suspicious part is the slug.”

Two identical slugs: failure on flush()

Now let us assemble the test itself. I will show a variant using TestEntityManager, because it clearly highlights the moment when we force the write to happen. In a real project you can also use repository.save(...), but the mechanism with flush() remains the same.

import jakarta.persistence.PersistenceException;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;

import static org.junit.jupiter.api.Assertions.assertThrows;

@DataJpaTest
class ArticleRepositoryDataJpaTest {

    private final TestEntityManager entityManager;

    ArticleRepositoryDataJpaTest(TestEntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Test
    void slug_must_be_unique() {
        // 1) Prepare the required related entity (so we do not fail on FK/NOT NULL by category)
        Category cat = new Category("java", "Java");
        entityManager.persist(cat);
        entityManager.flush(); // fix category in the DB

        // 2) The first article with the required slug is the "initial state"
        entityManager.persist(draftArticle("same-slug", cat));
        entityManager.flush(); // important: after this, the DB definitely already has slug = same-slug

        // 3) The second article with the same slug — prepare a unique violation
        entityManager.persist(draftArticle("same-slug", cat));

        // 4) We expect the error exactly on flush(), because that is where the real write to the DB happens
        assertThrows(PersistenceException.class, () -> entityManager.flush());
    }
}

What is important to read here with your eyes, not just “understand somehow in general.” We do the first flush() after saving the first article — thereby we fix the state “there is already slug = same-slug in the database.” Then we create a second article with the same slug and do the second flush(). The second write attempt is what should trigger the error.

One more note: the exception type can differ depending on the provider, version, and call path (through a repository or directly via entityManager). In a learning exercise it is perfectly normal to start with checking that “an exception of the required class was thrown on flush()” rather than trying to guess all wrappers in advance. It is more important to preserve the meaning: the uniqueness violation appears at the moment of the real write.

If you want something a bit closer to “Spring style,” you can write it so that it catches DataIntegrityViolationException (it is more common when you trigger a write through a repository):

import org.junit.jupiter.api.Test;
import org.springframework.dao.DataIntegrityViolationException;

import static org.assertj.core.api.Assertions.assertThatThrownBy;

@Test
void slug_must_be_unique_saveAndFlush_variant() {
    // Assume repository and category are prepared in the test setup.
    Article a1 = draftArticle("same-slug", category);
    repository.saveAndFlush(a1); // saveAndFlush = "save and synchronise with the DB right now"

    Article a2 = draftArticle("same-slug", category);

    // Expect a data integrity problem when trying to actually write to the DB
    assertThatThrownBy(() -> repository.saveAndFlush(a2))
            .isInstanceOf(DataIntegrityViolationException.class);
}

The readability here is even higher: saveAndFlush() clearly says that you “save and send to the database right now.” But in meaning it is the same lesson: without flush you may not see the problem in the test at all.

4. not null: checking through flush()

The not null story is similar, but beginners usually hit another trap here. They expect that if a field is null, then something should fail “right away.” But in JPA it may not fail immediately, but at write time. Moreover, sometimes you will get an exception not directly from the database, but from Hibernate, which understood that you are about to insert null into a non-nullable column. And that is normal: what matters is not “who noticed first,” but that bad data does not reach storage.

To make the test honest and precise, we again keep discipline: break only one field. For example, check that title cannot be null.

import jakarta.persistence.PersistenceException;
import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.assertThrows;

@Test
void title_must_not_be_null() {
    // Take a fully valid article so we do not get an error because of other constraints
    Article a = draftArticle("java-basics", category);

    // Break only one rule: NOT NULL for title
    a.setTitle(null);

    // persist on its own may not "say" anything — the real check will be on flush()
    entityManager.persist(a);

    // Expect failure at the moment of DB synchronisation
    assertThrows(PersistenceException.class, () -> entityManager.flush());
}

Why do we not call flush() earlier? Because we do not need to create an “initial state,” as in unique. Here one write attempt is enough. We intentionally do not touch slug, do not change summary, do not break category — so that the test answers one question: “does the persistence layer forbid saving an article without a title?”

It is very useful to say this out loud to yourself (yes, it looks strange, but it works): “This test checks only not null for title.” If you get the idea to add another check “and let’s make summary null too,” stop. You just tried to turn one clear test into a quest.

Now for an important nuance. In a project you can run into two sources of not-null behavior: JPA-level annotations (@Column(nullable = false)) and schema constraints (NOT NULL in the table). In educational data tests we do not always try to distinguish who exactly first “raised the red flag,” because the practical goal is to prove that you cannot get an incorrect state into the database. If you need specifically DB-level proof, then flush() still remains the point where the problem must surface.

5. Constraint exceptions: stable checks

When you first see a constraint error, your hand naturally reaches for an assertion on the exception message because it looks “informative.” But that is like treating a bus number as its VIN. Messages depend on the database dialect, the driver version, the locale, and the format of the constraint name in the migration. Today the tests are green, tomorrow you updated PostgreSQL or H2 — and suddenly the “expected string” no longer matches.

That is why in constraint tests we usually choose one of two levels of strictness.

The first level is very simple and often enough for a learning project: “an exception is thrown on flush().” It is not perfect, but it quickly teaches you the main thing: where the violation shows up.

// The simplest option: useful, but too broad (may hide setup errors)
assertThrows(Exception.class, () -> entityManager.flush());

The second level is more “professional”: we check that this is specifically a data-integrity problem, not a random NPE or a test setup error. Here DataIntegrityViolationException is a good fit when the write goes through a Spring Data repository, or PersistenceException when you call flush() directly.

// A more "domain-focused" check: we make sure the problem is really data integrity
assertThatThrownBy(() -> repository.saveAndFlush(article))
        .isInstanceOf(DataIntegrityViolationException.class);

Sometimes you may encounter a situation where in one environment you get DataIntegrityViolationException, and in another you get PersistenceException (or the same thing, but wrapped differently). In that case it is better not to fight types “in the name of theory,” but to check one of the expected variants. Just do not turn this into an endless list of “and this one happens too.” If the exception type is too unstable, that is a signal that the test should check a more general fact: the impossibility of writing.

At the same time, try not to swing to the other extreme and “catch everything.” A test that expects Exception.class while the setup makes half the domain null is not a constraint test; it is a magic fortune teller.

Discipline: one test, one constraint

Constraint tests are easy to spoil not technically, but methodologically. The most common way is to mix everything into one “universal” test: save an article, then a second one, then a third one, then make title=null, then try to break summary, then check count. Such a test does not increase confidence. It only creates fog: when it fails, you do not know which rule was actually violated.

The good news is that the discipline here is simple. If the test checks unique for slug, let it break only slug. If the test checks not null for title, let it break only title. And let the “this is where we expect the failure” point always be on flush() (or on saveAndFlush()), so the test reader can see: “we reached real contact with the DB.”

For readability it is also useful to keep the “valid fixture” small and standard. If you create an article with a different set of fields “just in case” every time, your brain will start spending energy not on checking constraints, but on figuring out what kind of Frankenstein you assembled this time. Better to have one simple helper draftArticle(...) that creates a valid object, and make targeted changes in the test itself.

6. Typical mistakes when testing unique and not null through flush()

Mistake #1: expecting a failure on persist() or save(), not on flush().
This is a very human mistake: you wrote the second persist(), and your brain says “okay, now the database should scream.” But JPA often delays the real SQL until synchronization with the database. As a result, you put assertThrows in the wrong place, the test “does not fail,” and it seems as if uniqueness does not work at all. The fix is always the same: put the exception expectation on flush() or use saveAndFlush().

Mistake #2: break several rules at once and then be surprised that the error is “not about that.”
You wanted to check uniqueness of slug, but accidentally created the second article without a title, and failed on not null. Formally the database punished you correctly, but the test became meaningless: it does not prove uniqueness. That is why constraint tests require almost laboratory-level accuracy: break one invariant, keep the rest valid.

Mistake #3: do not fix the initial state for unique and do not call flush() after the first write.
If you save two articles with the same slug in a row and do one shared flush(), you will still get a failure, but the diagnosis will be worse. It will be less obvious which operation exactly “broke” and why. One extra flush() after the first correct write makes the test clearer: “there is already such a slug in the database, the second write is forbidden.”

Mistake #4: trying to check the exception message text.
Today the message is one thing, tomorrow another — and you get a red test without any change in system behavior. This is especially “fun” when you change the DB or update the driver. In constraint tests it is better to check the type of exception if it is stable, or the very fact that writing is impossible, not the string.

Mistake #5: forgetting that in @DataJpaTest everything is usually rolled back, and without flush() the test may prove nothing.
If you simply do persist() and end the test, it can be green even with schema problems, because the actual write may never have happened. Rollback at the end of the test is convenient, but it can also “erase the traces.” That is why for any checks tied to database rules, flush() is your mandatory marker of reality.