You've spent five modules reading GreenStore's schema without having written a single line of it. You know that customers.email is UNIQUE, that order_lines.order_id is ON DELETE CASCADE, that rating is protected by a CHECK from 1 to 5 and that every primary key is id INTEGER GENERATED BY DEFAULT AS IDENTITY. You know it because you read it in lesson 01-06 and because PostgreSQL's errors have reminded you of it more than once. What you don't know yet is how all that gets declared.

Here begins the other side of the language: the DDL, the data definition sublanguage 01-01 talked about. In this lesson you'll learn the full syntax of CREATE TABLE —columns, types and the six constraints, one by one—, why it's worth naming them, how identity columns really work and how they differ from the good old SERIAL, what generated columns are, how to create temporary tables or tables built from a query, and how to drop them respecting the order referential integrity imposes. By the end you'll be able to read the greenstore.sql script from top to bottom understanding why each decision is what it is, and you'll be able to write one of your own.

1. From a dead catalogue to a live shop
2. Anatomy of CREATE TABLE
3. The constraints, one by one
4. Column level or table level
5. Naming constraints and why it matters
6. Identity columns: IDENTITY, SERIAL and the other engines
7. Generated columns
8. IF NOT EXISTS, CREATE TABLE AS SELECT and temporary tables
9. DROP TABLE and the order referential integrity imposes
10. A commented tour of GreenStore's DDL
11. Common Mistakes and Tips
12. Exercises
13. Conclusion

Module 4 closed with a sentence: "a shop that can't add a product, record an order, correct a price or cancel a purchase isn't a shop: it's a dead catalogue". Crossing to the other side starts here, and it starts with the most basic thing of all: before you can insert a row you need somewhere to put it.

A reminder from 01-01 about SQL's five sublanguages, now with module 5 placed on the map:

And a change of mindset worth internalising right now: the DDL defines the rules the database will enforce for you. Every NOT NULL, every CHECK, every FOREIGN KEY you write is a mistake your application will never be able to make, not today and not in three years' time, not from the code, not from a script, not from a console left open at three in the morning. It's the difference between trusting that everybody remembers to validate and making it impossible not to validate.

The general form:

Let's start with GreenStore's simplest table, categories, exactly as it stands in the course script:

Three columns and four decisions already made:

Notice that description carries no explicit NULL. In SQL, a column allows nulls unless you say otherwise. Writing description TEXT NULL is legal and means exactly the same thing; the course doesn't do it because it adds noise.

Inside psql, \d gives you back the real definition as the engine sees it:

That output is your best diagnostic tool for the whole module. It tells you the types, whether they allow nulls, the default values, the indexes backing the PK and the UNIQUE, and which other tables depend on this one. Get fond of it.

SQL has six declarative constraints. Here they are, in order from smallest to largest scope:

The simplest one and the most forgotten.

Any INSERT or UPDATE leaving that column without a value is rejected:

Deciding whether a column allows nulls isn't a technical question, it's a business one, and you already took it conceptually in 04-03. In GreenStore:

Practical rule: declare NOT NULL by default and allow nulls only when you have a clear answer to the question "what does it mean that there's nothing here?". A nullable column with no documented semantics is a guaranteed source of bugs.

The value used if the INSERT doesn't mention the column:

The three cases you'll meet in practice:

That nuance about functions matters and confuses a lot of people: DEFAULT CURRENT_DATE does not freeze the date on which you created the table. Each row gets the date of the day it was inserted.

And the three omitted columns (active, added_date and id itself) get filled with their default values. DEFAULT is what lets a short INSERT still produce a complete, coherent row.

DEFAULT isn't a constraint. It prevents nothing: it only fills gaps. If you insert NULL explicitly into a column with a DEFAULT, NULL is what gets stored (or it fails, if there's a NOT NULL). The DEFAULT only acts when you omit the column.

It marks the column —or the set of columns— identifying each row. It implies UNIQUE and NOT NULL at once, and there can only be one per table.

Simple form, at column level:

Composite form, necessarily at table level, because it affects several columns:

It reads: "a product can't carry the same tag twice". The composite PK is the business rule; no extra UNIQUE is needed.

This was exactly the alternative 01-05 raised for order_lines and that GreenStore didn't pick:

It would have meant "a product can only appear once in each order", and that would make it impossible to bill two lines of the same product with different discounts. That's why order_lines has its own id.

It prevents repeated values. Unlike PRIMARY KEY, you can have as many as you like and it does allow nulls.

When you try to register the same email twice:

Composite unique, at table level:

Careful with the semantics of a composite UNIQUE: it forbids repeating the combination, not each column separately. The same customer can review twenty products and the same product can receive twenty reviews; what there can't be is two rows with the same pair.

UNIQUE and NULLs: back to 04-03

Here three-valued logic returns. Since NULL isn't equal to NULL, a UNIQUE column can contain many null rows:

Three nulls coexist without any trouble in a UNIQUE column, while a second 'A' would have failed. From PostgreSQL 15 onwards you can change that behaviour:

With that, the second NULL would raise an error. It's useful when the null means "no code" and you want there to be only one such row.

Dialect note: this behaviour isn't universal. PostgreSQL, Oracle and SQLite allow several nulls in a unique column; SQL Server allows only one (it treats every NULL as equal for the purposes of the unique index). If you migrate a schema between engines, it's one of the quietest traps.

And a note you'll see developed in module 8: both PRIMARY KEY and UNIQUE are implemented by creating an index underneath. That's why they're cheap constraints to check and that's why they take up disk space. The structures and the cost, in due course.

It restricts the admissible values with a boolean expression. It's the most expressive constraint and the most underused.

GreenStore uses six kinds of CHECK. These are the real ones from the script:

Each one protects a rule the type on its own doesn't guarantee. SMALLINT accepts 7 and it accepts −3; the CHECK is what stops a 7-star review. NUMERIC(4,2) accepts 99.99; the CHECK is what stops a 9999 % discount.

A CHECK involving more than one column has to be declared at table level, because at column level it can only refer to its own:

Notice the cost IS NULL OR. Without it, the constraint would be useless in a subtle way: NULL <= price gives UNKNOWN, and a CHECK that gives UNKNOWN is considered satisfied. It's the most surprising rule about CHECKs:

That is, CHECK lets nulls through unless you forbid them expressly. If you want to demand a value, that's NOT NULL's job, not the CHECK's.

What a CHECK can't do:

• It can't query other tables. CHECK (price > (SELECT AVG(price) FROM products)) isn't valid: PostgreSQL rejects subqueries in a CHECK. For rules across tables there are foreign keys and, when those aren't enough, module 10's triggers.
• It can't use non-deterministic functions. CHECK (added_date <= CURRENT_DATE) is discouraged and PostgreSQL allows it but warns about it in the documentation: a row that's valid today could stop being valid tomorrow, and restoring a backup would fail for no apparent reason.

The constraint that connects tables and guarantees 01-05's referential integrity. It has two equivalent syntaxes:

The full form:

And the five possible actions, already familiar from 01-05, now with their syntax:

The NO ACTION / RESTRICT distinction is subtle and almost always irrelevant: NO ACTION lets another part of the same statement fix the situation before the final check; RESTRICT doesn't. In practice, both translate into "don't let me do it".

Applied to the real schema:

Two FKs in the same table with opposite actions, and both correct: a line doesn't exist without its order (CASCADE), but a product that has been sold must never be deletable (RESTRICT), because that would destroy the billing history.

About ON UPDATE: it fires when the parent's primary key changes. With surrogate keys it's never used, because an auto-increment id doesn't change. That's precisely one of the advantages of surrogate keys 01-05 listed. If your PK were natural (an article code, a tax number), ON UPDATE CASCADE would become essential.

Eleven foreign keys, four CASCADE, three SET NULL and four RESTRICT. You'll see the three actions at work, with counts before and after, in lesson 05-04.

Every constraint except NOT NULL and DEFAULT can be written in two ways.

At column level, right after the type:

At table level, at the end, as comma-separated elements:

When to use each:

If you don't name it, PostgreSQL generates a name following a fixed pattern:

That's why all the errors you've been seeing throughout the course have that shape: categories_name_key, reviews_rating_check, orders_customer_id_fkey.

The pattern works well while there's one constraint per column. As soon as there are two, PostgreSQL starts numbering:

demo_names_price_check1. Which of the two was that? Impossible to tell without looking at the definition. And now picture that name in an error message, at eleven at night, in a production log.

The version with explicit names:

And the error stops being a hieroglyph:

Applied to GreenStore, the orders table would look like this:

The three reasons that verbosity is worth it:

1. Readable error messages. chk_orders_status tells you which rule you've broken without opening the schema.
2. Migrations. To drop or modify a constraint you have to name it: ALTER TABLE orders DROP CONSTRAINT chk_orders_status; (lesson 05-06). With autogenerated names, every migration starts with an archaeological dig through information_schema.
3. Portability and reproducibility. Two environments created with slightly different scripts can end up with ..._check and ..._check1 swapped over. Explicit names eliminate that lottery.

A sensible and very widespread naming convention:

Why the course script doesn't use them. greenstore.sql deliberately goes without explicit names: that way the error messages you see in the lessons are the ones PostgreSQL generates out of the box, which is what you'll run into when connecting to somebody else's database. In a project of your own, name them.

Every PK in GreenStore looks like this:

Behind it there's a sequence: an engine object handing out increasing numbers. When you insert without supplying an id, PostgreSQL asks the sequence for the next value.

They're two variants with one important difference:

With ALWAYS:

GreenStore uses BY DEFAULT precisely because its loading script inserts the id values by hand (INSERT INTO categories (id, name, ...) VALUES (1, ...)), and that lets the course's examples talk about "customer 7" or "order 12" with stable, reproducible ids. The price of that convenience is the setval block at the end of the script, which you'll see explained in 05-02.

If your table's id is never going to be set by hand, GENERATED ALWAYS is safer: it removes the possibility of the sequence drifting at the root.

Before PostgreSQL 10 there was no IDENTITY and the SERIAL pseudo-type was used instead:

SERIAL isn't a real type: it's syntactic sugar PostgreSQL expands into three things:

You'll know how to recognise SERIAL in any old schema; write IDENTITY in yours.

It's one of the biggest divergences between dialects:

Dialect note: if you write DDL that has to work on several engines, the identity column will almost always be the first thing you have to fork. It's one of the reasons the migration tools in 05-06's closing section exist.

A generated column is a column whose value is computed from other columns of the same row. It isn't inserted or updated: the engine maintains it.

Now the expression you've been writing since 02-02 —quantity * unit_price * (1 - discount)— lives in the schema, not in every query:

And if you try to write into it:

The rules for generated columns in PostgreSQL 16:

• They have to be STORED (they're written to disk). VIRTUAL ones (computed on read) aren't supported yet.
• The expression has to be immutable: only columns of the same row and deterministic functions. No CURRENT_DATE, no subqueries, no other tables.
• It can't have a DEFAULT and it can't be an identity column.

It's a design decision with arguments on both sides:

GreenStore doesn't use one, for two teaching reasons and one practical one: writing the expression by hand is exactly what has taught you to think about amounts for three modules; discount as a fraction is already an explained design decision; and with 47 rows the saving would be zero. In a real system with millions of lines and a dozen applications querying it, the balance tips clearly in favour.

Dialect note: generated columns are fairly portable. MySQL 5.7+ has them with GENERATED ALWAYS AS (...) STORED | VIRTUAL; SQLite 3.31+ the same; SQL Server calls them computed columns (AS expression [PERSISTED]); Oracle uses GENERATED ALWAYS AS (...) VIRTUAL. The syntax varies little, but who supports VIRTUAL does vary, and PostgreSQL is one of those that don't.

It avoids the error if the table already exists:

It's a notice, not an error, and the statement is taken as good. It sounds convenient, but use it with care: if the table exists with a different definition, IF NOT EXISTS doesn't correct it, it ignores it silently. For scripts that create from scratch, DROP TABLE IF EXISTS followed by CREATE TABLE (which is what greenstore.sql does) is more honest: it guarantees the resulting structure is exactly the one you wrote. For real migrations, neither of the two: a versioned file and ALTER TABLE (lesson 05-06).

It creates a table from the result of a query, deducing columns and types:

The output isn't CREATE TABLE, it's SELECT 7: it's telling you how many rows it copied.

What CTAS copies and what it doesn't, and this is source of grief number one:

That is: expensive_products.id is not a primary key and it accepts duplicates and nulls. CTAS creates a container of data, not a well-defined table. It has three legitimate uses: quick backups before a dangerous UPDATE (you'll see exactly that in 05-03), intermediate analysis tables, and materialising the result of an expensive query.

If you only want the structure, with no data:

And if what you want is a copy with the constraints, the statement is a different one:

LIKE ... INCLUDING ALL does copy default values, constraints, indexes and identity —but not the data and not the foreign keys—. It's the closest thing to "cloning the table" PostgreSQL offers.

A temporary table exists only inside your session and disappears when you disconnect:

The properties that make it useful:

• Isolated: another session doesn't see it, and it can have a temporary table with the same name without interfering.
• Ephemeral: it's dropped when the session closes, or when the transaction ends if you add ON COMMIT DROP.
• It hides the real table: if you create a TEMP TABLE products, your queries will start reading the temporary one. Very handy for testing; very dangerous if you forget about it.

For single-step work, module 10's CTEs are usually better. Temporary tables shine when you need to reread the intermediate result several times.

• RESTRICT (the default): fails if any other object depends on the table.
• CASCADE: also drops the dependent objects (constraints in other tables, views…).
• IF EXISTS: doesn't complain if the table doesn't exist.

Try dropping categories with GreenStore loaded:

products.category_id references it. With CASCADE:

Read that NOTICE carefully: it hasn't dropped the products table, it has dropped its foreign key constraint. DROP TABLE ... CASCADE removes the dependencies, not the child tables. Even so, the result is that products is left without the barrier protecting its category_id, which is almost never what you wanted.

Referential integrity imposes a strict order, and it's the reason for greenstore.sql's structure:

That's why the course script starts like this:

Nine DROPs in reverse order of creation, with IF EXISTS (so it works the first time, when there's nothing there) and with CASCADE (in case the order failed). That's what makes the script idempotent: you can run it a hundred times and it will always leave the database in the same state.

You could also write it as a single statement, which sorts the order out by itself:

Warning. DROP TABLE is irreversible as soon as you commit the transaction and it doesn't ask. In PostgreSQL you can protect yourself with BEGIN; ... ROLLBACK;, because its DDL is transactional (lesson 05-06); in MySQL, you can't. And there's a special case with the reflexive relationships: customers.referred_by_id and employees.manager_id point at their own table, so no external table blocks them, but a DROP of customers without CASCADE will still fail because of orders and reviews.

Now for real: 01-06's script, read with everything you've learned. These are the decisions and the reasons behind them.

employees follows the same pattern with manager_id, plus one extra detail: salary NUMERIC(10,2) CHECK (salary >= 0) is nullable, because in practice not everybody has access to that piece of data.

You've already seen it in full in section 3.6. Just a reminder of why unit_price is there duplicating products.price: it isn't redundancy, they're different pieces of data. One is the current catalogue price, the other the price actually billed. Lines 1 and 4 of the data set prove it: €11.95 and €17.50 against today's €12.50 and €18.90. It's the deliberate denormalisation from 01-05, section 8.

There's a decision here worth looking at head-on: reviews has no constraint guaranteeing that the customer bought that product, nor preventing them from reviewing the same thing twice. You checked it in exercise 3 of 01-06: inserting a review from customer 14 about a product they never bought works.

It's intentional, and it holds an important lesson: a declarative constraint can only look at the row being inserted and, at most, at the existence of the key in another table. "This customer bought this product" requires walking through orders and order_lines, and that doesn't fit into a CHECK or a FOREIGN KEY. You'd need a trigger (module 10) or application logic.

The rule: the database protects the structural invariants; the application protects the process rules. Confusing the two leads either to naive schemas or to unmaintainable ones.

• Forgetting the NOT NULL. A column without it allows nulls, and finding out in production through an AVG returning a strange number is expensive. Declare NOT NULL by default and justify every exception.
• Using FLOAT or REAL for money. 0.1 + 0.2 doesn't give 0.3 in floating point. Always NUMERIC(10,2) (01-04).
• Believing a CHECK prevents nulls. A CHECK giving UNKNOWN is accepted. If you need a value, that's NOT NULL; if you need to cover the null inside the CHECK, write col IS NULL OR ....
• Putting ON DELETE SET NULL on a NOT NULL column. The table gets created, but the first deletion of a parent fails with null value ... violates not-null constraint. The two declarations are incompatible at the moment of truth.
• Using CASCADE out of convenience. A DELETE can propagate silently through half the database. Save it for genuine composition (05-04).
• Not naming your constraints. It works until the first migration; after that, every DROP CONSTRAINT starts with a search through information_schema.
• Creating tables in alphabetical order. The order is dictated by referential integrity: parents before children, and the other way round for dropping.
• Trusting CREATE TABLE AS SELECT as a faithful copy. It copies no PK, no UNIQUE, no CHECK, no FK, no identity and no indexes. That's what LIKE ... INCLUDING ALL is for.
• Confusing CREATE TABLE IF NOT EXISTS with a migration. If the table exists with a different structure, it ignores it silently and leaves you with two different environments.
• Tip: write your DDL in a versioned file, never by hand in the console. It's the seed of 05-06's migrations and the only way for two environments to be identical.
• Tip: \d table before writing any INSERT. It saves you half the module's errors.
• Tip: test your constraints. Write an INSERT that should fail and check that it fails. A constraint you've never seen fire may not be doing what you think.
• Tip: index your foreign keys. PostgreSQL indexes the PK and the UNIQUEs, but not the FKs. Without that index, JOINs and cascading deletes drag (module 8).

GreenStore wants to launch a discount coupon programme. Write the CREATE TABLE for the coupons table with these requirements, using explicitly named constraints:

• id integer, primary key generated by the engine, with no way of forcing it by hand.
• code text of up to 20 characters, mandatory and unique.
• description optional free text.
• discount a fraction between 0.01 and 0.50, mandatory (the same criterion as order_lines.discount).
• start_date and end_date, both mandatory, with end_date on or after start_date.
• max_uses optional integer; if it has a value, it must be greater than 0.
• current_uses mandatory integer, defaulting to 0 and never negative.
• customer_id optional: if the coupon is personal, it points at a customer; if that customer is deleted, the coupon should be left with no holder rather than disappearing.
• active mandatory boolean, defaulting to true.

For each of these six statements, say whether it's true or false and justify it in one sentence. If you can, check it by running it.

1. A UNIQUE column can't contain two rows with NULL in PostgreSQL.
2. PRIMARY KEY (order_id, product_id) allows a product to appear twice in the same order.
3. CHECK (cost <= price) rejects a row with cost set to NULL.
4. DEFAULT CURRENT_DATE stores the date the table was created.
5. CREATE TABLE copy AS SELECT * FROM products produces a table with the same primary key.
6. With GENERATED ALWAYS AS IDENTITY, GreenStore's loading script would work just the same.

This DDL has five problems. Find them, explain the consequence of each one and rewrite the corrected table.

Solution 1

The four points you had to get right:

Solution 2

Solution 3

The five problems:

And as a bonus, two improvements that aren't errors but are bad habits: SERIAL in new work and not a single named constraint.

You now know how to write the schema you'd spent five modules reading:

• CREATE TABLE declares columns with their type and their constraints, at column level (a single column) or at table level (several, necessarily).
• The six constraints: NOT NULL (obligation), DEFAULT (filling, not a constraint, evaluated on every insertion), CHECK (the domain of one row, and it accepts nulls because UNKNOWN is taken as good), UNIQUE (which does allow several NULLs in PostgreSQL, picking up 04-03 again), PRIMARY KEY (simple or composite) and FOREIGN KEY with its ON DELETE / ON UPDATE.
• Naming constraints (CONSTRAINT chk_orders_status ...) turns orders_status_check1 into a readable error message and makes 05-06's migrations possible.
• The identity columns: GENERATED BY DEFAULT (which lets you force the id, and that's why GreenStore uses it) against GENERATED ALWAYS (safer), against the old SERIAL; and the four incompatible syntaxes of MySQL, SQLite, SQL Server and Oracle.
• Generated columns (GENERATED ALWAYS AS (...) STORED) can put the line-amount formula into the schema, with their advantages and their cost.
• IF NOT EXISTS (which silently ignores a different definition), CTAS (which copies data but no constraints) and CREATE TEMP TABLE (isolated and ephemeral).
• DROP TABLE with RESTRICT/CASCADE, and the order referential integrity imposes: create from parents to children, drop from children to parents. It's the exact structure of greenstore.sql and the reason it's idempotent.
• And the commented tour of the real schema, with the boundary clearly drawn: the database protects the structural invariants; the process rules —"only someone who bought can review"— belong to the application or to a module 10 trigger.

You now have somewhere to put the data. In the next lesson, The INSERT Statement, you'll start putting it there: why you should always list the columns, how to insert forty rows in a single statement, what the setval block at the end of the course script really does, how to use RETURNING to get back the id the engine has just generated —the piece you were missing to record an order and its lines—, how to insert the result of a query with INSERT ... SELECT, and what exactly each of the four errors an INSERT can give you means.