Transactions & Constraints

Transactions & Constraints

Transactions make multiple operations atomic. Constraints enforce data rules at the database level — impossible to bypass.

Transactions

-- Transfer XP between users atomically
BEGIN;

UPDATE profiles
SET total_xp = total_xp - 100
WHERE id = 'sender-uuid'
  AND total_xp >= 100;  -- check balance

-- If first update affected 0 rows, something's wrong
-- Application code checks RETURNING and rolls back if needed

UPDATE profiles
SET total_xp = total_xp + 100
WHERE id = 'receiver-uuid';

COMMIT;
-- If any statement fails, ROLLBACK instead of COMMIT

Common Constraints

CREATE TABLE courses (
  id TEXT PRIMARY KEY,                    -- NOT NULL + unique
  track TEXT NOT NULL,                    -- required
  title TEXT NOT NULL,
  xp INT NOT NULL DEFAULT 0,
  duration INT NOT NULL CHECK (duration > 0),      -- must be positive
  level TEXT NOT NULL CHECK (level IN ('Basic', 'Masters', 'PhD', 'Next-Gen AI')),
  module INT NOT NULL CHECK (module >= 1),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Foreign Keys

CREATE TABLE progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  course_id TEXT NOT NULL REFERENCES courses(id) ON DELETE RESTRICT,
  xp_earned INT NOT NULL CHECK (xp_earned >= 0),
  completed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, course_id)  -- each user completes each course once
);

Savepoints (Nested Transactions)

BEGIN;
  INSERT INTO orders (user_id, total) VALUES ('uuid', 1000) RETURNING id;
  SAVEPOINT after_order;

  INSERT INTO order_items (order_id, product_id) VALUES (1, 99);
  -- If this fails, rollback to savepoint (not the whole transaction)
  ROLLBACK TO after_order;

COMMIT;