Entity & Attribute Constraints

Constraints are the rules your database uses to protect meaning. They ensure each row is uniquely identifiable, each column holds valid values, and relationships remain consistent as data changes. Good constraints move business rules into the data model so quality doesn’t depend on every application doing the right thing.

• NOT NULL: The attribute must have a value (e.g., Email cannot be empty for a user who needs sign-in).
• CHECK: A predicate that values must satisfy (e.g., CHECK (Discount BETWEEN 0 AND 1)).
• DEFAULT: Supplies a value when one isn’t provided (e.g., CreatedAt defaults to current timestamp).
• UNIQUE: No two rows share the same value (or combination) for the column(s) (e.g., unique SKU).

• PRIMARY KEY (PK): Chosen candidate key that uniquely identifies each row. Immutable and minimal.
• Alternate/Candidate Keys: Other unique identifiers enforced with UNIQUE constraints.

FOREIGN KEY (FK): Ensures child rows reference existing parent rows; optionally controls behavior on parent change:

• ON DELETE RESTRICT / NO ACTION: Prevent delete if children exist (preserve history).
• ON DELETE SET NULL: Keep child but clear the reference (use only if null is meaningful).
• ON DELETE CASCADE: Remove children when parent is deleted (use with care, typically for dependent facts).
• ON UPDATE CASCADE: Propagate key changes (rare if PKs are immutable).

• Reference (code) tables: Constrain values to curated sets (statuses, countries) via FK.
• Table-level CHECK: Express cross-column rules (e.g., ShipDate IS NULL OR ShipDate >= OrderDate).
• Generated/Virtual Columns: Derive values from other columns; still apply CHECK/UNIQUE if needed.

CREATE TABLE Customers (
  CustomerID   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Email        VARCHAR(254) NOT NULL UNIQUE,
  CountryCode  CHAR(2) NOT NULL,
  CreatedAt    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Countries (
  CountryCode  CHAR(2) PRIMARY KEY,
  Name         VARCHAR(80) NOT NULL
);

ALTER TABLE Customers
  ADD CONSTRAINT fk_customers_country
  FOREIGN KEY (CountryCode) REFERENCES Countries(CountryCode);

CREATE TABLE Orders (
  OrderID      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  CustomerID   BIGINT NOT NULL,
  OrderDate    DATE   NOT NULL,
  ShipDate     DATE,
  TotalAmount  DECIMAL(12,2) NOT NULL CHECK (TotalAmount >= 0),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
      ON DELETE RESTRICT,
  CONSTRAINT chk_order_dates
    CHECK (ShipDate IS NULL OR ShipDate >= OrderDate)
);

• Push rules to the model: Prefer PK/FK/UNIQUE/CHECK over app-only validation.
• Choose delete/update actions deliberately: Match legal, audit, and business expectations.
• Document constraints: Maintain a data dictionary so rules are discoverable and testable.
• Avoid triggers for validation unless constraints can’t express the rule or require side effects.

• Using nullable FKs where the relationship is mandatory.
• Relying on application code for uniqueness instead of UNIQUE constraints.
• Embedding meaning in identifiers (volatile, leaky) rather than using opaque keys.
• Leaving cross-column rules undocumented or only in code.

Constraints turn business rules into enforceable guarantees: PK/UNIQUE for identity, CHECK/DEFAULT/NOT NULL for valid values, and FK actions for relational integrity. A constraint-first mindset yields cleaner data and simpler applications.