CREATE TABLE

Before a single order can be stored, the shop needs somewhere to put it. CREATE TABLE is where a database design stops being a sketch on a whiteboard and becomes a real structure with rules the database enforces on every row, forever after.

The rule

Name the table, then each column: name, type, rules.

CREATE TABLE products (
    product_id  INTEGER       PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    category    VARCHAR(50),
    price       DECIMAL(10,2) NOT NULL,
    in_stock    BOOLEAN       DEFAULT TRUE
);

Read each line inside the brackets as name, type, rules:

product_id INTEGER PRIMARY KEY — every product has a unique, non-empty id.
name VARCHAR(100) NOT NULL — a product must have a name.
category VARCHAR(50) — no rules, so it may be left empty (NULL).
in_stock BOOLEAN DEFAULT TRUE — if an insert leaves this column out, it gets TRUE.

Column definitions are separated by commas, and the last one has no trailing comma. The statement ends with a semicolon.

Rules that involve several columns, or that point at another table, can be written as separate lines after the columns:

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL DEFAULT 1,
    order_date  DATE    NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    FOREIGN KEY (product_id)  REFERENCES products (product_id)
);

Running CREATE TABLE for a name that already exists is an error. PostgreSQL, MySQL and SQLite accept CREATE TABLE IF NOT EXISTS …, which quietly does nothing in that case — useful in setup scripts that may run twice.

The shortcut

Before typing, make a three-column list for each field: name | type | rules. The statement is then just that list wrapped in CREATE TABLE x ( … ); with commas between lines.

Designing the customers tableStep 1 of 4

List the names

customer_id, name, city, email, joined_on.

The most common error is a missing or extra comma. Check the ends of the lines, not the middles.

Drills

Answer fast — trust the rule.

quick drill

What happens if an INSERT into products leaves out in_stock, given in_stock BOOLEAN DEFAULT TRUE?

quick drill

A CREATE TABLE ends with price DECIMAL(10,2) NOT NULL, and then );. What is wrong?

quick drill

In the products example, which column may be left empty?

quick drill

A setup script creates customers and may be run twice. Which statement avoids an error on the second run?

Remember

Name the table, then each column: name, type, rules.
List name | type | rules first; the statement is that list wrapped in brackets.
Commas between columns, none after the last; DEFAULT fills columns an insert leaves out.

CREATE TABLE

CREATE TABLE

The rule

The shortcut

List the names

Drills

Remember

Practice — level up