Constraints and indexes

Every report trusts the data. Constraints are rules the database enforces on every insert and update, so data that breaks them never gets in (see Fraud and data quality).

The constraints you already have

• A primary key (policy_id SERIAL PRIMARY KEY) identifies each row: unique and never null.
• A foreign key (customer_id INT REFERENCES customers) must point at a row that exists: no policy for a customer who does not.
• NOT NULL makes a value compulsory.

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'policies'::regclass;

CHECK and UNIQUE

A check constraint is a condition every row must meet; a unique constraint refuses a second row with the same value. Add them to an existing table with ALTER TABLE; PostgreSQL first checks the rows already there, and refuses the constraint if any break it.

ALTER TABLE brokers
    ADD CONSTRAINT brokers_commission_sensible CHECK (commission_rate BETWEEN 0 AND 0.5);

-- Now this is refused:
UPDATE brokers SET commission_rate = 0.9 WHERE broker_id = 1;

To run such a script again, drop the constraint first: ALTER TABLE brokers DROP CONSTRAINT IF EXISTS brokers_commission_sensible;.

Indexes

An index is like the index of a book: instead of reading every row, PostgreSQL looks the value up. Primary keys and unique constraints get one automatically; foreign keys do not, so the joins you run every day may read whole tables.

CREATE INDEX IF NOT EXISTS claim_payments_claim_id ON claim_payments (claim_id);

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'claim_payments';