Data Types, CHECK, DEFAULT & Schema Evolution (ALTER / DROP)

Choosing a data type is a design decision

Every column needs a data type that matches both the *content* and the *intended use* of the data. The families you’ll use constantly:

- Character — CHAR(n) fixed-length, VARCHAR(n) variable-length (Postgres also has unbounded TEXT). - Numeric — INTEGER for whole numbers, NUMERIC(p,s) / DECIMAL(p,s) for exact decimals with precision p and scale s (use this for money — never floating point). - Temporal — DATE, TIME, TIMESTAMP. Storing dates as a date type unlocks date arithmetic you can’t do on text. - Boolean — BOOLEAN (TRUE / FALSE / unknown).

The classic mistake: storing zip codes or phone numbers as INTEGER because they “look numeric.” That drops leading zeros (01234 → 1234) and forbids extensions or dashes. Rule of thumb: if you’d never add two values together, store it as character.

Domain rules at the column: CHECK and DEFAULT

Constraints let the *database* enforce business rules so a buggy application can’t insert garbage:

gpa     NUMERIC(3,2) CHECK (gpa >= 0.0 AND gpa <= 4.0),
status  TEXT NOT NULL DEFAULT 'active'

CHECK rejects any row whose value violates the predicate; DEFAULT supplies a value when an INSERT omits the column. These are enforced by Postgres on every write — *not* by hopeful application code.

Worked example: evolving a table with ALTER

Schemas change. ALTER TABLE adds, widens, or drops columns and constraints on a table that already holds data:

-- 1. add a new optional column
ALTER TABLE students ADD COLUMN email TEXT;

-- 2. widen a numeric column (Postgres: ALTER COLUMN ... TYPE; Oracle says MODIFY)
ALTER TABLE students ALTER COLUMN gpa TYPE NUMERIC(4,2);

-- 3. add a domain constraint after the fact
ALTER TABLE students ADD CONSTRAINT chk_gpa CHECK (gpa >= 0 AND gpa <= 4.0);

What ALTER cannot do is redefine the primary key in place — the PK is the identity of every row and is referenced by foreign keys, so changing it ripples everywhere. (You must drop the dependent FKs and the PK constraint first, which is exactly why it’s avoided.)

DROP and the RESTRICT safety net

DROP TABLE removes the table, its data, indexes, and privileges — usually irreversibly. By default Postgres uses RESTRICT: it *refuses* to drop a table that another object still references. In the comp378 schema, DROP TABLE courses fails because sections, prereqs, and qualified all reference it — you’d have to add CASCADE to force it (and lose those dependents too). That refusal is a feature: it stops you from silently orphaning data.