SQL, Refreshed

Tables, types & constraints

Lesson 1 of 5

What you'll learn

Define a table with CREATE TABLE and column types
Use PRIMARY KEY, NOT NULL, and UNIQUE to enforce rules
See constraints as guarantees the database itself protects

A table is a set of rows, where every row has the same typed columns. You describe the shape once with CREATE TABLE, and Postgres enforces it on every insert forever after.

CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name  TEXT,
  age   INTEGER
);

Each column has a type: INTEGER for whole numbers, TEXT for strings, BOOLEAN, TIMESTAMP, and many more. Try to store the wrong kind of value and the insert fails — the type is a promise about what lives there.

Constraints are guarantees

A constraint is a rule the database refuses to violate. PRIMARY KEY means this column uniquely identifies the row (and is never null). NOT NULL means a value is required. UNIQUE means no two rows may share that value.

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
-- A second insert with the same email is REJECTED by UNIQUE.

The point is that these rules live in the database, not in your application code. Even a buggy app or a manual query can't sneak past them.

SERIAL is auto-incrementing

SERIAL isn't really a type — it's shorthand for an INTEGER column backed by a sequence that hands out 1, 2, 3… automatically, so you never assign id yourself.

A CREATE TABLE statement is really just a builder: you name the table, then accumulate column definitions. The challenge builds exactly that.

A fluent CREATE TABLE builder

Run it. Each method call adds a column; build() emits the CREATE TABLE string.

Loading editor…

Next: pulling rows back out with SELECT.

Next Querying: SELECT, WHERE, ORDER, LIMIT