FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Data Types, NULL and Constraints
Data Types, NULL and Constraints How PostgreSQL represents values and rejects invalid persistent states
Persistence becomes understandable when the database stops being magic.
understand the database before Prisma
Figure - Phase 07 isolates PostgreSQL fundamentals so later ORM code has a clear relational meaning.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Data Types, NULL and Constraints
Data types are part of the contract Choosing a column type tells PostgreSQL what values can be represented and what operations make sense. Good types reduce ambiguity and make invalid states harder to store.
Task title / description text Variable-length text without an arbitrary storage limit.
Completed flag boolean Represents true/false directly.
Priority number smallint / integer Numeric ordering; pair with CHECK for allowed range.
Money numeric(p,s) Exact decimal arithmetic when a monetary amount is truly needed.
Created timestamp timestamptz Timestamp with time-zone semantics normalized by PostgreSQL.
Identifier bigint identity or uuid Choice depends on system boundaries and identity strategy.
Structured flexible metadata jsonb Useful when structure is genuinely variable; not a substitute for normal columns.
Prefer modern identity columns for generated numeric IDs Generated identity example
CREATE TABLE practice_tasks ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL );
PostgreSQL supports identity columns as the SQL-standard way to generate values. The course uses them for fundamentals. Later, TaskFlow may use UUIDs where that better matches authentication or distributed identity requirements.
NULL means missing/unknown, not empty NULL is not the same as an empty string, zero or false. Comparisons involving NULL require special reasoning. Use IS NULL and IS NOT NULL, not = NULL. Correct NULL check
SELECT id, title FROM tasks WHERE due_at IS NULL;
SQL conditions can evaluate to TRUE, FALSE or UNKNOWN. A comparison such as due_at = NULL does not mean what JavaScript developers often expect.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Data Types, NULL and Constraints
Constraints encode durable rules Incoming row candidate data
Type checks is the value compatible?
NOT NULL / CHECK does the value satisfy local rules?
UNIQUE / PRIMARY KEY does identity remain valid?
FOREIGN KEY does the referenced row exist?
Accepted row database invariant preserved
Figure - Database constraints reject invalid states before they become persistent application data.
NOT NULL A required value cannot be absent.
CHECK A value or combination of values must satisfy an expression.
UNIQUE No two rows may share the constrained value combination.
PRIMARY KEY The table has one chosen unique non-null row identity.
FOREIGN KEY A relationship must point to a valid referenced row (unless nullable by design).
CREATE TABLE practice_tasks ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, completed boolean NOT NULL DEFAULT false, priority smallint NOT NULL DEFAULT 2, due_at timestamptz, CONSTRAINT practice_tasks_priority_check CHECK (priority BETWEEN 1 AND 3) );
Defaults are convenience, constraints are rules A default supplies a value when an INSERT omits that column. It does not make arbitrary incoming values valid. A CHECK or other constraint is what rejects invalid persisted values. Keep those responsibilities separate.
Do not use JSON for everything PostgreSQL has excellent jsonb support, but a flexible JSON document is not automatically the best model for strongly related business data. If the application routinely filters, joins or constrains a field, a normal typed column is usually easier to validate, query and index. JSON is useful when the domain is genuinely document-like or extensible.
ORMs do not remove the database. SQL literacy lets you reason about correctness, constraints, query cost, transactions, and the evidence stored in PostgreSQL.
Restate the goal of “Data Types NULL and Constraints” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: application intent → SQL statement → query planner → table/index → rows returned. Mark the one boundary this lesson changes.
Inspect the current repository state and record the exact files, route, table, or runtime involved. Avoid creating an abstraction before you can name the existing owner.
Implement the smallest vertical slice. Add one coherent change, run the narrowest verification command, and read the output before continuing.
Connect the slice to TaskFlow’s phase artifact: A TaskFlow relational sketch plus SQL exercises that create, query, join, update, and constrain representative data.. Verify behavior from the outside as a user or API client would.
Create one controlled failure that is safe to reproduce. Predict the symptom first, capture evidence, locate the earliest broken boundary, then fix only that cause.
Close the reference and rebuild or explain the core change from memory. Record what you would monitor or test before trusting the same change in production.
SELECT t.id, t.title, u.name AS assignee
FROM tasks AS t
LEFT JOIN users AS u ON u.id = t.assignee_id
WHERE t.workspace_id = $1
ORDER BY t.created_at DESC, t.id DESC;Do not copy this as a finished answer. Predict what each boundary does, type the smallest useful slice, run it, then compare the result with your prediction.
Capture EXPLAIN ANALYZE, row counts, filters, sort keys, and indexes before adding an index.
Inspect join cardinality and constraints; do not hide a modeling bug with DISTINCT until you understand it.
Without copying the final source, create a second small scenario that applies “Data Types NULL and Constraints” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Create a small local schema and insert data that exercises one-to-many and many-to-many relationships.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Missing composite uniqueness on membership-like tables
Predict the symptom before causing the failure. Then locate the earliest boundary where reality diverges from expectation.Why does this responsibility live here, what input does it trust, what output proves success, and what would go wrong if another layer owned it?
If your explanation is vague, repeat the boundary trace before moving on.Close the reference. Reproduce the core idea from memory or explain the exact request/data flow aloud. Then check your diff or notes for accidental shortcuts that bypass validation, authorization, error handling, or verification earned in earlier phases.