আগের lesson-এ আমরা initial PostgreSQL schema design করেছি:

products

inventory

orders

order_items

এখন সেই schema-এর সবচেয়ে গুরুত্বপূর্ণ structural guarantees-এর দুইটি নিয়ে গভীরে যাব:

Primary Key

Foreign Key

এগুলো শুধু SQL syntax নয়।

এগুলো database-কে বলে:

কোন row uniquely identifiable

কোন relationship valid

কোন duplicate state invalid

কোন referenced data অবশ্যই exist করতে হবে

আমাদের initial schema-তে:

products.id
→ Primary Key

inventory.product_id
→ Primary Key + Foreign Key

orders.id
→ Primary Key

order_items(order_id, product_id)
→ Composite Primary Key

এবং:

inventory.product_id
→ products.id

order_items.order_id
→ orders.id

order_items.product_id
→ products.id

foreign-key relationships আছে।

এই lesson-এর goal:

Primary keys, composite keys, foreign keys, uniqueness, এবং delete behaviour ব্যবহার করে persisted data-এর structural integrity কীভাবে protect করা যায়—এবং এই constraints কী করতে পারে না—সেটা পরিষ্কারভাবে বোঝা।

---

What Problem Does a Primary Key Solve?

Suppose products table contains:

id   | name
-----+----------
101  | Keyboard
102  | Mouse
103  | Monitor

আমরা যদি বলি:

Product 102

database precisely জানে কোন row-এর কথা বলা হচ্ছে।

এই identity guarantee আসে:

PRIMARY KEY

থেকে।

---

Primary Key Means Unique Row Identity

For Product:

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    ...
);

This guarantees:

id cannot be NULL

id must be unique

So this cannot happen:

id   | name
-----+----------
101  | Keyboard
101  | Mouse

PostgreSQL rejects the second row।

---

Why Identity Matters Beyond Lookup

Primary keys support:

reliable updates

reliable references

foreign keys

joins

ORM identity mapping

For example:

UPDATE products
SET active = FALSE
WHERE id = 101;

works because id = 101 identifies exactly one Product।

---

Domain Identity and Database Identity

Our domain already has:

ProductId

and:

OrderId

Database primary keys persist those identities।

Conceptually:

ProductId(101)
    ↕
products.id = 101

This is a clean alignment।

---

Primary Key Is Not Just an Auto-Increment Column

A common beginner model is:

Every table needs an auto-increment id.

That's not the actual rule।

The real question is:

What uniquely identifies one row representing this business fact?

Sometimes that is a generated ID।

Sometimes it is an existing meaningful key।

---

Inventory Is the Best Example

Our Inventory model says:

one Inventory state per Product

So:

ProductId

already uniquely identifies Inventory।

Schema:

CREATE TABLE inventory (
    product_id BIGINT PRIMARY KEY,
    available_quantity INTEGER NOT NULL,
    ...
);

We do not need:

id BIGINT GENERATED ALWAYS AS IDENTITY

as an additional key।

---

What Would an Extra Inventory ID Add?

Suppose we design:

inventory

id   | product_id | available_quantity
-----+------------+-------------------
5001 | 101        | 12

Now which identifier does the application actually care about?

InventoryId 5001?

or:

ProductId 101?

Our domain says ProductId।

The extra ID would mostly exist because of database habit।

That's unnecessary complexity।

---

Primary Key Should Match Row Identity

For Inventory:

product_id

is both:

the relation to Product

and

the identity of the Inventory row

This is a strong relational design।

---

Composite Primary Keys

A Primary Key does not have to contain one column।

Our order_items table uses:

PRIMARY KEY (
    order_id,
    product_id
)

This is a:

composite primary key

---

Why (order_id, product_id)?

Our business rule says:

One Order cannot contain the same Product twice.

So for a specific Order:

order_id = 1001

there can only be one row where:

product_id = 101

This combination:

(1001, 101)

uniquely identifies that OrderItem।

---

Example

Valid:

order_id | product_id | quantity
---------+------------+---------
1001     | 101        | 2
1001     | 102        | 1
1002     | 101        | 5

Invalid:

order_id | product_id | quantity
---------+------------+---------
1001     | 101        | 2
1001     | 101        | 3

The composite primary key rejects the second 1001 + 101 row।

---

This Reinforces Our API Contract

Remember Create Order rejects:

{
  "items": [
    {
      "productId": "101",
      "quantity": 2
    },
    {
      "productId": "101",
      "quantity": 1
    }
  ]
}

Application rejects this early।

Database also prevents it from being persisted accidentally।

That is:

defense in depth

---

Could We Use a Generated OrderItem ID Instead?

Yes।

For example:

order_items

id
order_id
product_id
quantity
unit_price

with:

id PRIMARY KEY

and separately:

UNIQUE(order_id, product_id)

This would also preserve the duplicate-line rule।

---

Which Design Is Better?

Both can be valid।

Generated OrderItem ID may make some ORM mappings simpler।

Composite key better reflects our current domain:

OrderItem has no independent identity

and avoids inventing one।

For this course we keep:

(order_id, product_id)

as the composite primary key unless implementation evidence gives us a good reason to change it।

---

Persistence Convenience Should Not Automatically Win

If JPA mapping a composite key requires a little more code, that alone is not enough reason to change the relational model।

But neither should we suffer large complexity merely for theoretical purity।

When we reach JPA relationships, we'll evaluate the actual trade-off।

The rule is:

Choose deliberately, not mechanically.

---

Primary Key and NULL

A Primary Key cannot be null।

This matters because identity cannot be:

unknown

for a persisted row।

For example:

orders.id

must always identify the Order once it is persisted।

---

Generated Identity Before Persistence

A new Order may temporarily exist in application memory before PostgreSQL assigns:

OrderId

depending on our persistence construction strategy।

This creates a design consideration:

Does the domain require ID before persistence?

Or can persistence assign it?

We will deal with this when mapping JPA entities and repositories।

Don't distort the database key strategy before seeing the actual implementation need।

---

What Problem Does a Foreign Key Solve?

Suppose Inventory contains:

product_id = 999

but no Product 999 exists।

Without a foreign key, PostgreSQL may happily store this orphan row।

Foreign key tells PostgreSQL:

This value must reference an existing row in another table.

---

Inventory Foreign Key

Schema:

CONSTRAINT inventory_product_fk
    FOREIGN KEY (product_id)
    REFERENCES products(id)

Now:

inventory.product_id = 101

is valid only if:

products.id = 101

exists।

---

Example Failure

Products:

id
---
101
102

Attempt:

INSERT INTO inventory (
    product_id,
    available_quantity
)
VALUES (
    999,
    10
);

PostgreSQL rejects it because Product 999 does not exist।

That is:

referential integrity

---

Why Application Validation Is Not Enough

We could have application code:

check Product exists

then insert Inventory

But bugs happen।

Other code paths may appear।

Migrations or operational scripts may write data։

A database foreign key protects the relationship regardless of which application path performs the write।

---

Foreign Keys Are Especially Valuable in a Modular Monolith

Our modules share one PostgreSQL database।

That means the database knows relationships such as:

Inventory → Product

OrderItem → Order

OrderItem → Product

Using foreign keys lets us get strong local referential integrity without network coordination।

This is an advantage of our current architecture।

---

OrderItem to Order

Constraint:

CONSTRAINT order_items_order_fk
    FOREIGN KEY (order_id)
    REFERENCES orders(id)

This guarantees:

An OrderItem cannot exist without an Order.

So PostgreSQL rejects:

order_id = 9999

if no such Order exists।

---

This Matches Aggregate Ownership

Our domain says:

OrderItem belongs to Order

Relationally:

order_items.order_id
→ orders.id

captures that ownership relationship structurally।

---

OrderItem to Product

Constraint:

CONSTRAINT order_items_product_fk
    FOREIGN KEY (product_id)
    REFERENCES products(id)

guarantees every OrderItem references a locally known Product।

Because Products are deactivated instead of deleted, historical OrderItem references remain valid।

---

Why Preserve the Product Reference?

OrderItem already stores:

unit_price

for historical pricing।

So why keep:

product_id

too?

Because the OrderItem still represents:

This quantity of this Product was ordered at this purchase-time price.

Historical price snapshot does not erase Product identity।

---

Foreign Key Does Not Mean Historical Fields Are Derived

Even with:

order_items.product_id
→ products.id

we must not calculate historical unit price using:

products.price

Foreign key preserves identity relationship।

unit_price preserves historical price।

They solve different problems।

---

CustomerId Has No Foreign Key

Our orders table contains:

customer_id TEXT NOT NULL

but no:

customers

table।

Therefore there is intentionally no local foreign key।

---

Does That Mean CustomerId Has No Integrity?

Not exactly।

Its integrity comes from a different boundary:

external authentication system
    ↓
authenticated identity
    ↓
CustomerId
    ↓
Order

PostgreSQL guarantees:

customer_id is present

but cannot verify a relationship to a table the application does not own।

---

Don't Create a Fake Customer Table for Foreign-Key Comfort

Bad reasoning:

Every foreign identifier should reference a table, so let's create customers.

That would invent local Customer ownership we intentionally rejected।

Database modeling must respect system boundaries।

---

Referential Integrity Stops at System Boundaries

Inside our PostgreSQL-owned data:

Product

Inventory

Order

OrderItem

foreign keys can be strong।

Across an external identity system:

CustomerId

we rely on application/integration guarantees instead।

This is normal in real systems।

---

Foreign Keys and Insert Order

Relationships influence persistence ordering।

For example, to create an Order with OrderItems:

Order row must exist
before OrderItem rows can reference its ID

So persistence conceptually performs:

INSERT Order
    ↓
obtain OrderId
    ↓
INSERT OrderItems

within one transaction।

---

Product Must Exist Before Inventory

Similarly:

Product
    ↓
Inventory

means Inventory row cannot be inserted before the referenced Product exists।

This is okay because that relationship is real।

---

Foreign Key and Transaction Visibility

Within one PostgreSQL transaction, we can insert:

parent row

then:

child row

before committing।

Other transactions do not need to observe an inconsistent final state।

Transactions and foreign keys work together।

---

What Happens When a Referenced Row Is Deleted?

This is where foreign-key delete behaviour matters।

Suppose:

order_items.product_id
→ products.id

and someone tries:

DELETE FROM products
WHERE id = 101;

while OrderItems still reference Product 101।

What should happen?

---

Our Desired Behaviour: Reject the Delete

For current requirements:

Product is deactivated

not physically deleted

Therefore historical references should block accidental physical deletion।

We intentionally do not configure:

ON DELETE CASCADE

for Product relationships।

---

Default Restrictive Behaviour

Without a cascade action, PostgreSQL will generally prevent deleting the referenced Product while dependent rows exist।

That's exactly what we want।

It protects history from accidental destructive operations।

---

Why ON DELETE CASCADE Would Be Dangerous

Suppose:

Product 101

has OrderItems in 10,000 historical Orders।

If Product foreign key were:

ON DELETE CASCADE

then deleting Product could automatically delete related OrderItems।

Suddenly historical Orders are corrupted or emptied।

Database convenience has destroyed business data।

---

Cascade Is Not Inherently Bad

ON DELETE CASCADE is useful when child data has no independent meaning once parent is deleted।

For example, some temporary child records may genuinely need cascading deletion।

But the key question is:

Does deletion of the parent mean these child facts should cease to exist?

For historical OrderItems:

No.

So no cascade।

---

What About Order → OrderItems?

Our domain also does not support physical Order deletion।

So there is no current business need for:

ON DELETE CASCADE

there either।

If a future operational purge requirement appears, it deserves its own design rather than preconfiguring destructive cascades now।

---

ON DELETE SET NULL

Another option is:

ON DELETE SET NULL

That would turn:

order_items.product_id

into:

NULL

when Product is deleted।

But our OrderItem requires Product identity।

Losing it would damage historical meaning।

So this also doesn't fit।

---

Referential Integrity and Soft Deletion

Our Product deactivation approach:

active = false

keeps the row intact।

That means foreign keys remain valid automatically।

This is one reason lifecycle state is often better than physical deletion for referenced business entities։

---

Primary Key vs Unique Constraint

Primary key guarantees unique row identity।

But sometimes we need additional uniqueness that is not the table's primary identity।

Example:

If OrderItem had generated ID:

id PRIMARY KEY

we would still need:

UNIQUE (
    order_id,
    product_id
)

to enforce:

one Product line per Order

So:

Primary Key

and:

Unique Constraint

solve related but distinct problems।

---

Unique Constraint Means "No Duplicate Fact"

Suppose we had:

email

inside an application-owned User table and email were globally unique।

A UNIQUE constraint could represent that fact even if primary identity were numeric UserId।

Our current schema doesn't need that example, but the distinction matters।

---

Inventory Uses Primary Key Instead of Separate Unique Constraint

We could write:

id BIGINT PRIMARY KEY,
product_id BIGINT UNIQUE

But since ProductId itself is Inventory identity:

product_id BIGINT PRIMARY KEY

is cleaner।

One constraint expresses both identity and uniqueness।

---

OrderItem Composite PK Is Also a Business Constraint

Our composite Primary Key simultaneously means:

this combination identifies the row

and:

duplicate Product lines are impossible

This is a strong example of relational identity and business structure aligning nicely।

---

Foreign Key Data Types Must Be Compatible

If:

products.id

is:

BIGINT

then:

inventory.product_id

and:

order_items.product_id

should use compatible types।

Do not create:

products.id BIGINT

but:

order_items.product_id TEXT

without a strong reason।

Consistent types make joins and integrity straightforward।

---

Indexing and Keys

Primary keys are backed by indexes in PostgreSQL so uniqueness and lookup can be enforced efficiently।

Unique constraints also create supporting uniqueness structures।

But foreign keys are different:

The referencing columns are not automatically guaranteed to have every index we may need for our application queries.

We'll cover indexes deliberately in the index lesson।

---

Don't Add Duplicate Indexes Blindly

Because Primary Key already supports lookup on its key, creating:

CREATE INDEX ON products(id);

would typically duplicate an index already implied by the primary key।

Understand existing constraints before adding indexes।

---

Composite Key Ordering Matters

Our OrderItem Primary Key:

(order_id, product_id)

naturally supports operations beginning with:

order_id

which is useful because a common query is:

load all items for Order 1001

This key order aligns well with our access pattern।

---

Why Not (product_id, order_id)?

That would still uniquely identify rows।

But our most important relationship/query is:

Order
→ its OrderItems

So putting:

order_id

first is more natural for both domain ownership and likely lookup patterns।

Schema details should reflect access patterns where possible।

---

Product-to-OrderItem Queries

What if later we need:

find all Orders containing Product 101

The composite key (order_id, product_id) may not optimally serve that query by Product alone।

If that becomes an important query, we can add an index on:

product_id

later।

Don't redesign the primary key for a query we don't currently need।

---

Foreign Keys Do Not Enforce Every Domain Rule

Suppose OrderItem references:

Product 101

Foreign key verifies Product exists।

It cannot tell us:

Product was active when Order was created

That's a temporal business rule।

UseCase checks Product state during Create Order।

---

Foreign Key Does Not Check Inventory

OrderItem → Product foreign key cannot ensure:

Inventory was sufficient

when the Order was created।

Again:

CreateOrderUseCase

coordinates that operation।

---

Primary Key Does Not Enforce Order Lifecycle

orders.id being unique tells us nothing about whether:

UNPAID → PAID

or:

PAID → CANCELLED

is valid।

Keys protect identity and relationships, not workflow semantics।

---

Constraints Are Powerful but Narrow

A useful mental model:

Primary Key
→ Who is this row?

Unique Constraint
→ Can this fact appear twice?

Foreign Key
→ Does this referenced row exist?

CHECK
→ Does this row satisfy a local condition?

Domain / UseCase
→ Is this business operation valid?

Each has a different role।

---

Don't Push Workflow Into Foreign-Key Tricks

Suppose someone proposes separate tables:

unpaid_orders

paid_orders

cancelled_orders

just so foreign keys can somehow encode transitions।

That would massively complicate persistence for a simple lifecycle।

Our current:

orders.status

plus domain transitions is clearer।

Use relational constraints where they naturally fit։

---

Referential Integrity During Product Deactivation

When Product becomes inactive:

active = false

nothing happens to:

inventory.product_id

order_items.product_id

Their foreign keys remain valid।

This is correct because Product still exists historically।

Only orderability changes।

---

Inventory Can Exist for Inactive Product

Should deactivating Product delete Inventory?

No such requirement exists।

Product may remain:

inactive

with:

Inventory quantity = 5

Customer browse filters it out because Product isn't orderable।

Keeping Inventory does not violate relational integrity।

---

Cancellation Restores Inventory Regardless of Product Active State

Our accepted behaviour says:

cancel Order
→ restore consumed Inventory

even if Product has since been deactivated।

Foreign-key design supports that:

Product row still exists
Inventory row still exists

The cancellation workflow can restore quantity normally।

This is another reason not to destroy Inventory during Product deactivation।

---

What If Inventory Row Is Missing?

Our relational model expects:

one Inventory state per Product used for ordering

But foreign key only says:

if Inventory exists, Product must exist

It does not guarantee:

every Product has an Inventory row

This is important।

---

Foreign Keys Are Directional

This constraint:

inventory.product_id
→ products.id

means:

Inventory requires Product.

It does not mean:

Product requires Inventory.

Enforcing that every Product has Inventory would be a different cross-table rule।

---

Do We Need to Enforce Product → Inventory Existence?

Our requirements don't yet say that Product creation must atomically create Inventory state।

Therefore we should not introduce complex database enforcement for it।

Orderability already requires:

Product active

AND

Inventory > 0

A Product without Inventory simply won't satisfy that browse/order path until Inventory is configured appropriately।

---

Order Must Have at Least One OrderItem

Similar directionality exists for:

order_items.order_id
→ orders.id

It guarantees every OrderItem has an Order।

It does not guarantee every Order has at least one OrderItem।

That rule remains protected by:

Order domain invariant

CreateOrderUseCase transaction

---

Parent-Existence Rules Can Be Harder Relationally

Rules like:

every Order must have at least one child row

span multiple rows/tables and are not naturally represented by a simple foreign key।

This is why not every invariant belongs in a declarative relational constraint।

---

Temporary Integrity Inside a Transaction

During Create Order transaction:

1. insert Order
2. insert OrderItems
3. commit

there may be a moment inside the transaction where the Order row exists before its items are inserted।

That's fine।

The important invariant is:

no incomplete Order becomes committed visible state

Transaction atomicity handles this।

---

Generated Keys and OrderItems

Because orders.id is generated by PostgreSQL, persistence needs the generated ID before inserting:

order_items.order_id

JPA can manage this mapping for us later।

But conceptually it's important to understand what the ORM is doing।

The database relationship still requires a concrete parent key।

---

Could Application Generate IDs Before Insert?

Yes।

If we used UUID or another application-generated identifier, we could know OrderId before persistence।

Then parent/child inserts can both use the already-known value।

But our modular-monolith requirements do not justify changing identity strategy solely for that convenience।

Generated numeric IDs remain reasonable।

---

Foreign-Key Violations Should Not Become Raw API Errors

Suppose buggy code attempts to persist OrderItem for a missing Product।

PostgreSQL might raise a foreign-key constraint violation।

We must not send:

order_items_product_fk violation

to the API client।

That's an internal persistence failure signal।

---

Translate Meaning at the Right Boundary

If missing Product was an expected business case:

CreateOrderUseCase

should normally detect:

Product not found

before persistence and produce:

PRODUCT_NOT_FOUND

at the API boundary।

A later foreign-key violation for the same condition usually indicates:

race

bug

unexpected persistence problem

and needs appropriate handling rather than blindly mapping constraint names to client codes।

---

Do Not Build API Logic Around Constraint Names

Avoid:

if (
    exception.getMessage()
            .contains(
                "order_items_product_fk"
            )
) {
    return PRODUCT_NOT_FOUND;
}

This tightly couples HTTP behaviour to database exception text।

Application should detect expected business conditions intentionally।

Database constraints remain last-line integrity protection।

---

When Constraint Mapping Can Be Useful

There are cases where a database uniqueness constraint is the authoritative concurrency-safe way to detect a race।

For example, creating a value that must be globally unique।

Then persistence layer may translate a specific constraint violation into a meaningful application conflict।

But that translation should be deliberate and based on stable persistence semantics—not string parsing scattered across Handlers।

---

Inventory Concurrency Is Another Example

Later we may use database mechanics to prevent concurrent overselling।

A database failure/zero-row update may become:

INSUFFICIENT_INVENTORY

at application level।

Again:

Repository/persistence
→ interprets DB mechanics

UseCase
→ understands operation outcome

HTTP layer
→ maps to 409 Problem

not Controller parsing PostgreSQL messages।

---

Foreign-Key Cycles

Complex schemas can create:

A references B

B references A

which complicates inserts/deletes।

Our current model is intentionally simple and mostly directional:

Product
    ↑
Inventory

Product
    ↑
OrderItem
    ↓
Order

No unnecessary circular relational ownership is introduced।

---

Keep Ownership Clear

Order owns OrderItems conceptually।

Product does not own Orders।

Inventory references Product but isn't nested inside Product persistence automatically।

Clear relationship direction reduces mapping confusion।

---

Constraint Names Should Be Descriptive

We currently use names such as:

inventory_product_fk

order_items_order_fk

order_items_product_fk

order_items_pk

These are useful when:

reading schema

debugging migrations

inspecting production errors

Prefer names that reveal the relationship।

---

Avoid Names Like fk_1

This:

fk_1

provides almost no operational context।

During an incident, seeing:

order_items_product_fk

is much more useful।

---

Primary Key Names

PostgreSQL can generate primary-key constraint names automatically।

For some tables we explicitly name composite constraints such as:

order_items_pk

because readability matters।

We do not need to manually name every trivial constraint if conventions already make them clear, but explicit names can be useful in migrations and operations।

---

Referential Actions Should Be Explicit Decisions

Whenever we create a foreign key, ask:

What should happen if parent is deleted?

Should child deletion cascade?

Should deletion be rejected?

Can reference become null?

Never add:

ON DELETE CASCADE

because a framework tutorial used it।

---

Our Current Referential Actions

For:

inventory.product_id
→ products.id

we want Product physical delete blocked while Inventory references it।

For:

order_items.product_id
→ products.id

we definitely want history protected।

For:

order_items.order_id
→ orders.id

Order physical deletion is not a business operation।

Therefore restrictive/default behaviour fits all three current relationships।

---

What About ON UPDATE CASCADE?

Our generated Product/Order primary keys are immutable identities।

We do not support:

change ProductId 101 → 200

or:

change OrderId

So cascading primary-key updates solve no real requirement।

Identity should remain stable।

---

Primary Keys Are Immutable Business References

Once Product is:

ProductId 101

renaming Product or changing price should not change its ID।

Likewise Order lifecycle transitions do not change:

OrderId

Identity remains stable while state changes।

---

Never Use Mutable Business Attributes as Primary Keys Casually

Suppose Product name were used as primary key:

"Keyboard"

Then renaming to:

"Mechanical Keyboard"

would become an identity migration across all relationships।

That's unnecessary coupling।

Stable generated IDs avoid this।

---

External CustomerId Is Different

CustomerId is externally controlled but expected to be stable。

We store it as ownership reference because that external system defines the identity contract।

We do not derive ownership from:

customer email

display name

which may change।

Stable identifiers are critical for relational references even when no local FK exists।

---

Primary Keys and API URLs

Our API might expose:

GET /api/v1/products/101

and:

GET /api/v1/orders/1001

These IDs ultimately map to database primary keys through domain IDs।

But HTTP code should still operate with:

ProductId

OrderId

rather than direct SQL assumptions।

---

Do Not Expose Composite Persistence Keys Accidentally

OrderItem does not have its own public endpoint:

/order-items/{orderId}/{productId}

just because its database key is composite।

The API design already decided OrderItems are nested parts of Order।

Persistence identity does not automatically define HTTP resources।

---

API Resource Design and DB Keys Are Related but Separate

Database asks:

How is this row uniquely identified?

HTTP API asks:

What operations/resources should clients interact with?

Sometimes they align directly।

Sometimes they don't।

Do not derive API design mechanically from table keys।

---

Constraints Also Protect Operational Scripts

Even in a well-designed application, production systems may eventually have:

migration scripts

data repair scripts

manual administrative SQL

Database constraints still apply there।

That's one reason structural integrity should not live only in Java।

---

But Constraints Can Also Block Bad Migrations

Suppose a migration accidentally attempts to create an invalid foreign reference।

PostgreSQL rejects it instead of silently corrupting data।

This makes strong constraints valuable during system evolution too।

---

Adding Foreign Keys to Existing Data

In a brand-new schema, foreign keys are straightforward।

In an existing production database with legacy bad data, adding a new foreign key may fail because orphan rows already exist।

Then engineers need:

data cleanup

migration planning

safe rollout

Our course starts from a new schema, but later change requests should remember this operational reality।

---

Constraint Changes Are Schema Changes

Changing:

Primary Key

Foreign Key

Unique Constraint

requires a database migration।

These are not JPA-only changes।

That's another reason schema changes belong to Flyway।

---

JPA Must Respect the Schema

Later when mapping:

ProductEntity

InventoryEntity

OrderEntity

OrderItemEntity

the ORM should map our accepted keys and relationships accurately।

We should not allow a convenient JPA default to silently:

add extra IDs

remove uniqueness

change nullability

invent cascades

that contradict the schema design।

---

JPA Cascade Is Not the Same as Database Cascade

This distinction becomes important later।

JPA can have concepts such as:

CascadeType.PERSIST

CascadeType.REMOVE

Database has:

ON DELETE CASCADE

These are different mechanisms at different layers।

Never assume configuring one means the other automatically behaves the same way।

We will revisit this during JPA relationships।

---

Example: Creating an Order

Let's see how the keys cooperate।

Application creates:

Order
CustomerId = C-10
items:
    Product 101 × 2
    Product 102 × 1

Persistence transaction:

INSERT orders
    ↓
PostgreSQL generates OrderId 1001

Then:

INSERT order_items
(1001, 101, 2, 100.00)

INSERT order_items
(1001, 102, 1, 50.00)

Foreign keys verify:

Order 1001 exists

Product 101 exists

Product 102 exists

Composite PK verifies:

Product 101 appears only once in Order 1001

Product 102 appears only once

Everything commits together।

---

Example: Duplicate Product Bug

Suppose application bug tries:

(1001, 101, 2)

(1001, 101, 1)

Second row violates:

PRIMARY KEY(order_id, product_id)

Database refuses to persist invalid duplicated lines।

The transaction should roll back।

---

Example: Missing Product Bug

Suppose application tries:

order_id = 1001

product_id = 999

but Product 999 doesn't exist।

Foreign key rejects the row।

Again, invalid relational state never commits।

---

Example: Delete Product Accident

Suppose Product 101 has historical OrderItems।

Someone runs:

DELETE FROM products
WHERE id = 101;

Foreign key from order_items prevents deletion।

Historical references survive।

The correct business action remains:

deactivate Product

---

Example: Delete Order Accident

Suppose Order 1001 has two OrderItems।

Someone tries:

DELETE FROM orders
WHERE id = 1001;

Foreign key prevents deleting the parent while children reference it।

Again, historical business state is protected।

---

Example: Inventory Without Product

Attempt:

Inventory(ProductId = 999, quantity = 10)

without Product 999।

Foreign key rejects it।

This protects the structural rule:

Inventory belongs to a real Product

---

Database Integrity Is Necessary but Not Sufficient

After all these constraints, database can still contain a structurally valid but business-invalid state if application workflows are wrong।

For example:

Order status = PAID

while payment provider never actually confirmed payment।

All keys and foreign keys are valid।

But business truth is wrong।

That's why:

correct UseCases

domain rules

integration handling

remain essential।

---

Another Example

Database might contain:

UNPAID Order

with valid OrderItems and valid Product foreign keys।

But maybe Inventory was not decreased due application bug।

All relational constraints could still pass।

Cross-aggregate workflow correctness requires transaction/application logic।

Keys are not enough।

---

Think in Layers of Protection

Our final system will protect data through several levels:

HTTP validation
    ↓
UseCase workflow
    ↓
Domain invariants
    ↓
Repository persistence strategy
    ↓
Primary Keys
Foreign Keys
Unique Constraints
CHECK constraints
    ↓
PostgreSQL transaction

No single layer solves everything।

Together they create a resilient system।

---

Primary and Foreign Key Review Checklist

For every table, ask:

What uniquely identifies one row?

Is that identity stable?

Do I really need a generated surrogate ID?

Is there a natural/composite identity already?

Which columns reference other persisted concepts?

Can PostgreSQL enforce those relationships?

What should happen if the parent is deleted?

Would cascade destroy historical information?

Does a uniqueness constraint reinforce a real business rule?

Am I accidentally trying to encode workflow behaviour
with structural keys?

---

Common Mistake 1 — Every Table Gets an Auto-Increment ID

Inventory already has ProductId as its row identity।

---

Common Mistake 2 — Composite Key Avoided Only Because ORM Mapping Is Less Familiar

Persistence convenience should be considered, but not automatically override a clean relational model।

---

Common Mistake 3 — No Foreign Keys Because "The Application Checks It"

Database constraints protect all write paths and provide stronger persisted integrity।

---

Common Mistake 4 — ON DELETE CASCADE Added by Default

Historical Product and Order references must not disappear automatically।

---

Common Mistake 5 — Customer Table Created Only to Support a Foreign Key

External identity boundaries should remain external।

---

Common Mistake 6 — Foreign Key Treated as Business Validation

Product existence is not the same as Product orderability।

---

Common Mistake 7 — Primary Key Treated as Authorization

Guessable numeric IDs still require proper ownership/permission enforcement।

---

Common Mistake 8 — Mutable Attribute Used as Identity

Names, prices, and statuses change; identity should remain stable।

---

Common Mistake 9 — Constraint Violation Message Returned Directly to API Client

Database vocabulary remains internal।

---

Common Mistake 10 — Assuming Keys Guarantee Workflow Correctness

Keys protect structure; UseCases and transactions protect business workflows।

---

Our Current Key Design

products

PRIMARY KEY
→ id

Purpose:

stable Product identity

---

inventory

PRIMARY KEY
→ product_id

FOREIGN KEY
→ product_id references products.id

Purpose:

one Inventory row per Product

Inventory cannot exist for a missing Product

---

orders

PRIMARY KEY
→ id

Purpose:

stable Order identity

customer_id is an external ownership reference, not a local foreign key।

---

order_items

PRIMARY KEY
→ (order_id, product_id)

Purpose:

one Product line per Order

Foreign keys:

order_id
→ orders.id

product_id
→ products.id

Purpose:

OrderItem belongs to a real Order

OrderItem references a real Product

---

Engineering Principle

The core principle:

Primary keys define durable row identity; foreign keys protect relationships between persisted facts. Neither should be chosen as boilerplate.

Another:

Use relational constraints aggressively where the business rule is structural and unambiguous, but don't mistake referential integrity for complete business correctness.

And:

Delete behaviour is part of data integrity. A convenient cascade can be far more dangerous than a missing annotation when historical business data matters.

---

Summary

In this lesson, we learned that:

• Primary keys uniquely identify persisted rows.
• Primary keys guarantee uniqueness and non-null identity.
• Generated Product and Order IDs align naturally with their domain identities.
• Not every table needs a generated surrogate ID.
• Inventory is naturally identified by ProductId.
• Composite primary keys can represent meaningful relational identity.
• (order_id, product_id) naturally identifies an OrderItem in our v1 model.
• The OrderItem composite key also reinforces the no-duplicate-Product-lines rule.
• A generated OrderItem ID would require an additional uniqueness constraint to preserve the same business rule.
• Foreign keys protect referential integrity between persisted rows.
• Inventory cannot reference a Product that does not exist.
• OrderItems cannot reference missing Orders or Products.
• CustomerId has no local foreign key because Customer identity is externally owned.
• Referential integrity ends at system ownership boundaries.
• Foreign keys are directional: Inventory requiring Product does not automatically mean every Product must have Inventory.
• OrderItem foreign keys do not guarantee every Order has at least one item.
• Cross-table business invariants still need domain logic and transaction boundaries.
• Product and Order physical deletion should be restricted because historical data must remain intact.
• ON DELETE CASCADE should never be added automatically.
• Primary keys are not authorization mechanisms; sequential IDs still require ownership and role checks.
• Foreign keys confirm existence, not business state such as Product orderability or Inventory sufficiency.
• Constraint violations should not leak directly through API responses.
• Primary keys, foreign keys, uniqueness, CHECK constraints, application logic, and transactions form complementary layers of data protection.

Next lesson:

Spring Data JPA

There we will connect our Java application to PostgreSQL through JPA, Hibernate, and Spring Data, understand what each one actually does, and define how they fit behind our existing Repository boundary without letting the framework become our domain architecture.