One quick word, before this chapter begins. Throughout these lessons we keep saying "the bed." A bed is the garden's word for what SQL calls a table — a named, rectangular collection of rows. Each row in the bed is one flower; each column is one trait every flower carries (id, species, color, height). Different beds hold different things — a rose bed holds roses, a herb bed would hold herbs. The bed we've been working in is named rose_bed, and it looks like this in the garden and on the page:

The rose bed is just one of many beds the gardener tends. Every bed has its own shape — what it holds, and which traits the gardener writes down for each item in it. A rose bed cares about color and height; a herb bed cares about scent; a vegetable bed cares about how many days until harvest. Same idea — a named rectangular collection of rows — different shape:

For two chapters the rose_bed has been a given — five flowers, four colors, columns already there. You walked it with SELECT, then you planted, pruned, and pulled flowers from it. But you never asked: where did the bed come from?

Behind the back of the garden is a glass-roofed workshop. Inside, the gardener stands at a wooden workbench with a small card in her hand — a trait card. Before any rose is planted, the card is filled out: this bed will hold flowers, each with an id, a species, a color, a height. The id is the unique tag — no two flowers share it. The species and color must always be filled in. The height must be a positive number.

That card is the schema. SQL's verb for drafting one is CREATE TABLE. This chapter is about it — and about the rules (constraints) that the trait card can stamp onto the bed.

A schema (a trait card) has three parts:

• The name of the bed — what to call this table
• The columns — what trait every flower will have on its tag
• The constraints — rules the bed enforces on every flower planted in it

The smallest possible trait card is just a name and a list of columns with their types. Add nothing more, and the bed accepts almost anything. Add constraints, and the bed begins to refuse flowers that don't measure up.

Don't worry about the details yet — we'll meet each constraint in its own page. Just notice the shape: CREATE TABLE name (...); with each column on its own line inside the parentheses, name first, type second, constraints after.

The greenhouse starts empty. Press Run query to create the bed and watch the trait card appear on the right. Then try the chip below to plant a test flower with INSERT — without constraints yet, anything goes.

Every column has a type — a declaration of what kind of value should live there. How strictly that declaration is enforced varies by database: most databases reject a text value placed in an INTEGER column outright, while SQLite (and this lesson's engine) are relaxed and will accept it. Either way, the type still documents your intent — and on the day you move the bed to a stricter database, that documentation becomes enforcement. Three types carry most of the work:

• INTEGER — whole numbers (1, 2, -3, 200)
• TEXT — words and sentences, in single quotes ('Old English')
• REAL — numbers with a decimal point (1.5, 19.25)

SQL has a few more (BOOLEAN, DATE, TIME, BLOB, NUMERIC) and dialects add their own, but these three cover almost everything in our chapters. Pick the type that matches the shape of the value — a height in cm is INTEGER, an average is REAL, a species name is TEXT.

By default, every column is optional — leave it empty (or pass NULL) and SQL is fine. Stamp NOT NULL on a column and that changes. The bed will refuse a flower that has no value there.

Run the CREATE TABLE below, then click the chip to try planting a rose with no species — you'll see a friendly rejection.

Every flower in the bed needs to be tellable apart from every other. The PRIMARY KEY is the column that does that — its values must be unique across the whole bed, and it can't be left blank. Usually it's an integer called id.

Run the CREATE TABLE and try inserting two roses with the same id. The second one will bounce off — the bed already has that tag.

Some columns hold values that should never repeat in a bed — a species name, a label, a registration number. The UNIQUE constraint tells the bed: refuse any new value that's already in this column.

Below, species is marked UNIQUE. Try planting two roses with the same species name; the second one will bounce off.

A column with a DEFAULT has a fallback. Insert a flower without specifying that column, and SQL fills it in for you.

Below, color defaults to 'pink' and height defaults to 20. Try the chip to plant a rose with just id and species — the rest will be filled in.

A CHECK constraint is a rule the bed enforces on every attempted plant. The condition uses the same WHERE-style operators you already know.

Below, height must be positive and at most 300 cm; color must be one of four allowed values. Try planting a rose with a wild value to see the bed turn it away.

So far every bed has stood alone. But gardens have relationships. Imagine a second bed alongside the rose bed — a bed of gardeners, each with their own id and name. Now imagine a third trait card, flower_beds, a catalogue of every bed in the garden — rose bed, herb bed, vegetable bed — where each entry says which gardener tends it. The way to say "this bed belongs to that gardener" is to store the gardener's id on the flower_beds row.

But storing a number is just a number. Nothing stops you from writing gardener_id = 99 on a flower_beds row when no gardener with id 99 actually exists. The pointer would point at nothing, and the bed would never notice.

A FOREIGN KEY turns that pointer into a promise. The bed refuses any value for the referencing column that doesn't match an id in the referenced table. Two trait cards, with a line drawn from one to the other — and the line is now enforced.

Sometimes the trait card you drafted needs to change after the bed is planted. Maybe you forgot a column. Maybe a column turned out to be useless. SQL's ALTER TABLE lets you change the schema without redrafting from scratch.

DROP TABLE doesn't just empty the bed — it removes the trait card entirely. After it, the bed simply isn't there. Any rows are gone. Any foreign keys that pointed at it now point at nothing.

This is the most destructive single statement in SQL. Run it on the wrong table and it cannot be undone (outside a transaction). Many real teams disable DROP TABLE permissions in production for exactly this reason.

The greenhouse is yours. Draft a trait card from scratch, insert rows to test the constraints, and watch what gets accepted or rejected. Reset wipes the greenhouse clean.

Try the beds we sketched on Page 2 — a herb_bed with (id, name, scent, perennial), a veg_bed with (id, kind, days_to_harvest) — or invent something the garden doesn't have yet. Several beds can coexist in the greenhouse at once, each with its own trait card.

In the Greenhouse, no bed is dug and no flower is yet planted. Only a card is drafted, on which the columns of the future bed are named and the rules it will obey are stamped. By such cards, every bed in the garden is shaped before its first seed.

That closes Spring. Beyond it lies Summer, with two further gardens. One is called the Flight Path: it is travelled by bees, between the hive and the open flower, and it is never the same path twice. The other is called the Sorting Table: there the day's basket is set down, and its contents are placed into smaller baskets, one color to each. For today, the workbench is wiped clean, and the trait card filed away.