Schema invariants: unique, FK, indexes

1. Schema invariants in mapping design

When we talk about “mapping design”, it is easy to get stuck at the Java level: where to place @Column, which FetchType to choose, how to name a field. But Hibernate ultimately talks to the database. That means the model has a second “skeleton” — the schema: constraints and indexes that turn rules from wishes into laws.

The worst thing about invariants is that they do not break gracefully. They break quietly. First you get two products with the same sku. Then someone calls findBySku() and “accidentally” gets the first match. Then the sales report starts being calculated for the “wrong” product. Then you spend the evening with coffee, logs, and a philosophical question: “Why is reality so cruel if I already checked uniqueness in the service?”

Let’s be honest: service code can validate a lot, but it is not the only entry point for data. In real life, data is touched by migrations, admin panels, batch scripts, and sometimes even “I’m just going to look through psql”. And even more importantly: an application can run in several instances in parallel, which means two transactions can decide at the same time that sku is free.

A good mental model is this: service checks and validations are a “polite conversation” with the user and the code. Schema constraints are a “turnstile” that physically blocks invalid data, even if you ask very convincingly.

A small diagram showing why “checked in the service” and “guaranteed” are different things:

flowchart TD
    %% Two parallel transactions can both see "free" at the same time (race condition)
    A["Transaction #1: existsBySku(sku)=false"] --> B["INSERT product(sku)"]
    C["Transaction #2: existsBySku(sku)=false"] --> D["INSERT product(sku)"]

    %% The outcome depends on whether the invariant is enforced in the DB itself
    B --> E{"Is there UNIQUE(sku) in the DB?"}
    D --> E

    E -->|Yes| F["One INSERT succeeds, the other fails on constraint violation"]
    E -->|No| G["Both INSERTs succeed, and you get duplicate data"]

This is especially important in our course because the project baseline fixes the following: Flyway manages the schema, we do not use ddl-auto, and “automatic schema creation from annotations” is disabled. So if an invariant matters, it must be in migrations. An entity annotation may be useful as documentation, but it does not replace a real ALTER TABLE in the database.

And so that PromotionCampaign does not split into three alternative schemas later, in this part we keep the working SINGLE_TABLE variant with the promotion_campaign table: that is what UNIQUE, FK, and indexes will apply to.

2. Unique constraints: business keys and “unique pairs”

Uniqueness is the most understandable type of invariant: “there must be only one such sku”, “an email cannot belong to two customers”. In code, this is easy to express with an existsBy... check, but that approach breaks under concurrency and manual data fixes. That is why UNIQUE lives in the schema — where you cannot accidentally bypass it.

It is important to separate two concepts that people constantly mix up. PRIMARY KEY is the uniqueness of the row’s technical identifier. It is always needed. UNIQUE is the uniqueness of a business rule, that is, something that “must not repeat” in the domain. In the Commerce Persistence Lab project, there are quite a few such rules, and they are not decorative: they determine whether we can trust our “natural keys” (sku, email, orderNumber, code).

Here is what this looks like as a compact “invariant map” for the schema:

How to enforce this in Flyway

Since Flyway manages the schema for us, the most honest way is to add UNIQUE directly in a migration. Pay attention to constraint names: a human-readable name later becomes a human-readable error message. It is a small thing, but it saves nerves.

Example for Product.sku and Customer.email:

-- Guarantee uniqueness of the business key for the product (sku)
alter table product
  add constraint uk_product_sku unique (sku);

-- Guarantee that one email cannot belong to two customers
alter table customer
  add constraint uk_customer_email unique (email);

Example for the promotion campaign code:

-- Campaign code is a business key; we use it to look up the campaign in code
alter table promotion_campaign
  add constraint uk_campaign_code unique (code);

For a link entity, uniqueness of the pair is literally “the first rule of good manners”:

-- Prevent duplicating the same link (product ↔ category)
alter table product_category_assignment
  add constraint uk_product_category unique (product_id, category_id);

And here is a very important stock invariant that will later save the model from “two inventory cards for one product” many times over:

-- Exactly one stock row per product (product_id)
alter table inventory_item
  add constraint uk_inventory_product unique (product_id);

What you get in practice

The first thing you get is the impossibility of silently corrupting data. If someone tries to insert a duplicate, PostgreSQL honestly says “no”. The error will look something like this (simplified, but recognizable):

ERROR: duplicate key value violates unique constraint "uk_campaign_code"
Detail: Key (code)=(SPRING-2026) already exists.

The second thing is more honest repository semantics. For example, the findByCode() method for promotion campaigns implies “0 or 1 record”. It is exactly UNIQUE(code) that turns this from “we hope so” into “it will always be that way”.

A mini repository example (short, without unnecessary magic):

package com.example.commerce.promotion.repository;

import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;

public interface PromotionCampaignRepository extends JpaRepository<PromotionCampaign, Long> {
    // We return Optional because the invariant expects "0 or 1" record
    Optional<PromotionCampaign> findByCode(String code);
}

The third thing is a small but nice bonus: in PostgreSQL, UNIQUE is usually implemented through a unique index, which means queries by sku, email, orderNumber, and code automatically get performance support. The important thing here is not to confuse cause and effect: the purpose of UNIQUE is correctness; faster reads are a side benefit, not the reason the constraint exists.

3. Foreign key: relationships and referential integrity

If UNIQUE protects “no more than one”, then FOREIGN KEY protects “no references into nowhere”. ORM is excellent at drawing relationships with annotations, but the database does not read your @ManyToOne. Without FK, it is easy to create orphan rows by accident and then wonder why joins behave like a horror movie.

From the database’s point of view, a foreign key is a rule: “a value in column X is either NULL or must reference an existing row in table Y.” And this rule is a key part of what we call “data consistency”. Hibernate can load a graph, can skip loading a graph, can create a lazy proxy — but if the table contains target_category_id=123 and there is no category with id=123, then you do not have a “fetching problem”, you have broken data.

FK using PromotionCampaign as an example

In our limited inheritance case, PromotionCampaign has a target category (in the project it is called targetCategory). It makes sense to store this as target_category_id and enforce the relationship with an FK constraint:

-- Disallow references to a "non-existent category"
alter table promotion_campaign
  add constraint fk_campaign_target_category
  foreign key (target_category_id) references category(id);

If, by the domain’s logic, a campaign must always be tied to a category, then you also add NOT NULL to the column. If a “campaign for the whole catalog” is allowed, the column may be nullable, but the FK is still useful: it protects against references to a category that does not exist.

FK and the reality of Hibernate code

In Java code, you express this in the usual way with @ManyToOne and @JoinColumn. It is important to understand that nullable=false on @JoinColumn is part of mapping documentation and a hint for DDL generation, but in a project with Flyway it will not create NOT NULL automatically.

A short fragment to show the intent:

import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.FetchType;

@ManyToOne(fetch = FetchType.LAZY) // LAZY: load the category only when it is actually needed
@JoinColumn(name = "target_category_id") // Map the field to the FK column in the table
private Category targetCategory;

If you need NOT NULL specifically, you fix it in the migration, and the annotation remains as a “traffic sign” so that the developer does not try to save a campaign without a category.

Indexes on FK columns

This is where confusion often starts, so let me say it directly: a foreign key is not the same as an index. FK protects correctness; an index speeds up access. In PostgreSQL, a foreign key does not create an index automatically, and that is normal: it is not always needed.

But there is a practical rule that usually saves you from unpleasant surprises: if you often do joins or filtering by a foreign key, then an index on the FK column is almost always justified. For promotion_campaign.target_category_id, an index makes sense if you often look up campaigns by category or join campaigns with categories.

4. Indexes: choose for queries, not for anxiety

The word “index” in development triggers two extremes. The first: “indexes are magic, let the DBA handle it.” The second: “let’s index everything, including the developer’s mood.” We need a third path: an index is a data structure for a specific query, and you pay for it with every insert and update.

Very simply, an index is a “fast reference book” that helps the database avoid scanning the whole table. But every reference book has a price: it must be stored on disk, kept in memory, and updated with every write. That means an index is not “free acceleration” but a conscious choice that almost always speeds up reads and almost always makes writes more expensive.

To avoid turning indexing into a religion, it is helpful to start not with “which columns should be indexed” but with “which queries do we actually have”. In our project, there are a few typical read cases that explain the basics of indexing well:

where sku = ?

unique index (via UNIQUE)

where email = ?

unique index (via UNIQUE)

where order_number = ?

unique index (via UNIQUE)

where code = ?

unique index (via UNIQUE)

where active=true
        and valid_from<=? and valid_to>=?

composite index for the filter

where target_category_id=?

index on the FK column

An index for “active campaigns by date”

This is our key example for PromotionCampaign. Imagine that the service needs to get a list of campaigns active on a specific day. At the JPQL level, it looks very ordinary:

import java.time.LocalDate;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

public interface PromotionCampaignQueryRepository extends Repository<PromotionCampaign, Long> {

    // Find campaigns that are enabled and fall within the date range
    @Query("""
        select c
        from PromotionCampaign c
        where c.active = true
          and c.validFrom <= :day
          and c.validTo >= :day
        """)
    List<PromotionCampaign> findActiveOnDay(@Param("day") LocalDate day);
}

In SQL, this will almost always boil down to filtering by active, valid_from, and valid_to. For such a query, it makes sense to add an index starting with equality (active = true), and then include the dates:

-- Composite index for the typical "active + date range" filter
create index idx_campaign_active_dates
  on promotion_campaign (active, valid_from, valid_to);

There is an important engineering idea here: an index is a “support” for a specific kind of filter. If you never read campaigns by activity and dates, the index will just take up space and slow down updates. If you read them often, it is a very practical optimization, and with no “Hibernate tuning” at all: PostgreSQL uses the index, and Hibernate simply generates queries.

Index on target_category_id

If you have queries like “give me campaigns for category X”, then a separate index on the FK column also becomes logical:

-- An index speeds up filtering and joins by target_category_id
create index idx_campaign_target_category
  on promotion_campaign (target_category_id);

And again, an important point: do not index a column just because “it is an FK”. An index is needed if you actually filter/join on that column often, or if you have operations where the lack of an index creates load. In the educational project, we try to make every index explainable by at least one real query.

The risks of indexing everything

Because writes are also part of application life. A wide entity + many indexes = every INSERT and UPDATE starts updating not only the table but also a whole “bundle of reference books”. For a small dataset, you will not notice it. For a large one, you will suddenly notice it, and very emotionally.

Indexes are a good example of an engineering compromise: when you add an index, it is as if you are telling the database, “I promise these reads matter.” And the database replies, “Okay, then I promise writes will be a bit more expensive.”

5. PromotionCampaign: constraints and indexes in Flyway

Let’s put everything together on our educational PromotionCampaign hierarchy. We keep it in the SINGLE_TABLE variant with a single promotion_campaign table, so now we will add to that table exactly the rules that reflect the domain invariants and the actual reads.

What we want to guarantee at the schema level

For PromotionCampaign, there are several things that must be rock solid. The campaign code must be unique, otherwise findByCode() turns into a lottery. The link to the target category must be valid, otherwise we will get campaigns for a “ghost category”. And finally, we have the typical read case “active campaigns by date”, which calls for an index.

This can be expressed as a small table so it is immediately clear why we are doing this:

UNIQUE(code)

FOREIGN KEY(...) REFERENCES ...

INDEX(active, valid_from, valid_to)

INDEX(target_category_id)

Entity annotations: useful, but not as the source of truth

In Java code, you can declare uniqueness and indexes through @Table(uniqueConstraints = ..., indexes = ...). It is nice to read and helps you remember the meaning of the schema, but again: if you have Flyway and ddl-auto is disabled, these annotations will not create constraints in the database “by magic”.

A mini fragment that shows the intention:

import jakarta.persistence.Entity;
import jakarta.persistence.Table;
import jakarta.persistence.Index;
import jakarta.persistence.UniqueConstraint;

@Entity
@Table(
    name = "promotion_campaign",
    // Document the business invariant: code must be unique
    uniqueConstraints = @UniqueConstraint(name = "uk_campaign_code", columnNames = "code"),
    // Document the important read case: filter by active + date range
    indexes = @Index(name = "idx_campaign_active_dates", columnList = "active, valid_from, valid_to")
)
public abstract class PromotionCampaign { }

The purpose of this fragment is documentary: it helps future readers see that code is not just a string, but a business key, and active/valid_from/valid_to are not just fields, but an important filter. But the real guarantee still lives in migrations.

Flyway migration: where rules become laws

In migrations, we fix the same things, but now in the “language of the database.” An approximate set of DDL lines follows (in a real file, you would place them next to table creation):

-- Uniqueness of the campaign business code
alter table promotion_campaign
  add constraint uk_campaign_code unique (code);

-- Referential integrity: target_category_id must point to an existing category
alter table promotion_campaign
  add constraint fk_campaign_target_category
  foreign key (target_category_id) references category(id);

And indexes for our reads:

-- Index for the "active campaigns by date" query
create index idx_campaign_active_dates
  on promotion_campaign (active, valid_from, valid_to);

-- Index for the "campaigns by category" query
create index idx_campaign_target_category
  on promotion_campaign (target_category_id);

Notice that we do not add indexes “just in case” on every column, and we do not try to turn promotion_campaign into a DBA art exhibit. Our goal is simpler and more pragmatic: the schema should support the domain rules and the main queries that we actually write in code.

6. Typical mistakes when working with invariants

Schema mistakes rarely look like “your annotation is wrong”. Usually they show up as strange duplicate data, sudden production failures, or slow lists that “were fine yesterday”. Below are a few typical pitfalls around UNIQUE, FOREIGN KEY, and indexes that are worth recognizing.

Mistake #1: uniqueness implemented only as a service-layer check.
A check like “if existsBySku() returns false, then we can insert” looks logical until you run the application in two instances or simply receive two concurrent requests. Both transactions see “free” and insert a duplicate. UNIQUE in the database turns this situation from “silently corrupted data” into “one operation honestly failed”, and that is a much healthier kind of error.

Mistake #2: believing that annotations will create constraints in the database by themselves.
@Column(unique = true), @JoinColumn(nullable = false), @Table(indexes = ...) — great hints… if you generate the schema from Hibernate. In a project where Flyway manages the schema, they are nothing more than documentation. If there are no migrations, then the database has neither UNIQUE, nor NOT NULL, nor indexes. As a result, the code “looks at” a pretty entity, while the data lives by different rules.

Mistake #3: not giving constraints and indexes clear names.
When a constraint is called something like product_sku_key or, worse, a random auto-generated string, problem analysis turns into a puzzle. But uk_product_sku or fk_campaign_target_category read like a sentence in normal language. This is especially important when you see an exception in the logs: there are already enough mysteries in ORM, no need to add another one.

Mistake #4: indexes added because “indexes are useful”.
An index without a query behind it is like a first-aid kit with every medicine in the world “just in case”. Yes, something may be useful someday… but it is heavy and expensive to carry. In the database, this shows up as extra disk usage and slower INSERT/UPDATE. A good index can almost always be explained by a concrete query shape, ideally one you actually see in repository code.

Mistake #5: there is a foreign key, but no index on the FK column, even though reads by it are frequent.
FK makes data correct, but it does not make joins fast. If you constantly read campaigns by target_category_id and there is no index, the database will have to scan the table more often. That is not a “disaster” for small data, but it is a classic source of sudden slowdown when an educational dataset turns into a production one.

1

Task

Hibernate deep-dive, level 16, lesson 4

Locked

Schema constraints and index for PromotionCampaign

Schema constraints and index for PromotionCampaign

Open

1

Task

Hibernate deep-dive, level 16, lesson 4

Locked

Unique Pair and Foreign Keys for ProductCategoryAssignment

Unique Pair and Foreign Keys for ProductCategoryAssignment

Open

1

Survey/quiz

Entity Modeling, level 16, lesson 4

Unavailable

Entity Modeling

Inheritance, associations, and indexes

Open