GGistDev

Schema and Constraints

Define table structures and enforce data integrity with constraints.

CREATE TABLE

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Primary and foreign keys

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES users(id),
  total NUMERIC(12,2) NOT NULL CHECK (total >= 0)
);

UNIQUE and CHECK

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT non_negative_total CHECK (total >= 0);

DEFAULT values

ALTER TABLE users ADD COLUMN active BOOLEAN NOT NULL DEFAULT true;

ON DELETE / ON UPDATE actions

CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL,
  qty INT NOT NULL CHECK (qty > 0)
);

Choose CASCADE/RESTRICT/SET NULL based on lifecycle needs.

Naming conventions

Name constraints explicitly for clearer errors and easier migrations.

Summary

  • Model relationships with foreign keys; add UNIQUE, CHECK, and DEFAULT for integrity
  • Pick appropriate ON DELETE/UPDATE actions for referential behavior
←PreviousModifying Data
NextIndexes→