⬅️ Back to Model Index

Data Integrity Constraints

Data integrity constraints at the database level protect data quality and consistency.

Primary Keys

Purpose: Uniquely identify each record

SUPPLIER.ID - Unique supplier identifier
INVENTORY_ITEM.ID - Unique item identifier
STOCK_HISTORY.ID - Unique history entry identifier
APP_USER.ID - Unique user identifier

Implementation:

@Id
@Column(name = "ID", nullable = false)
private String id;  // UUID or generated ID

Database Constraint:

PRIMARY KEY (ID)

Benefits: - Every record uniquely identifiable - Foreign keys can reference primary keys - Prevents duplicate records - Enables fast lookups by ID

Unique Constraints

Purpose: Prevent duplicate values in specific columns

SUPPLIER.NAME - No two suppliers with same name
INVENTORY_ITEM.NAME - No two items with same name
APP_USER.EMAIL - No two users with same email

Implementation:

@Column(name = "NAME", unique = true)
private String name;

// Multiple columns unique together
@Table(uniqueConstraints = {
    @UniqueConstraint(columnNames = {"SUPPLIER_ID", "NAME"})
})

Database Constraint:

UNIQUE (NAME)
UNIQUE (EMAIL)

Benefits: - Enforces business rules at database level - Prevents data anomalies - Database rejects invalid inserts/updates - Faster validation than application-level checks

Foreign Keys

Purpose: Enforce referential integrity between tables

INVENTORY_ITEM.SUPPLIER_ID → SUPPLIER.ID
STOCK_HISTORY.ITEM_ID → INVENTORY_ITEM.ID

Implementation:

@Column(name = "SUPPLIER_ID", nullable = false)
private String supplierId;

@ManyToOne
@JoinColumn(name = "SUPPLIER_ID", foreignKey = 
    @ForeignKey(name = "fk_item_supplier"))
private Supplier supplier;

Database Constraint:

FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER(ID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE

Benefits: - Ensures data consistency across tables - Prevents orphaned records - Database enforces relationship validity - No invalid cross-table references

NOT NULL Constraints

Purpose: Enforce required fields

SUPPLIER.ID - Required
SUPPLIER.NAME - Required
SUPPLIER.CREATED_BY - Required
INVENTORY_ITEM.QUANTITY - Required
INVENTORY_ITEM.UNIT_PRICE - Required
STOCK_HISTORY.REASON - Required

Implementation:

@Column(name = "NAME", nullable = false)
private String name;

@Column(nullable = false)
private String createdBy;

Database Constraint:

NOT NULL

Benefits: - Ensures required data is always provided - Prevents NULL values in critical fields - Database rejects incomplete records - Improves query reliability

Constraint Summary Table

⬅️ Back to Model Index