How Do You Design a CREATE TABLE?

A CREATE TABLE is four decisions in one statement. The columns and their types, the primary key, the constraints that protect the data, and the defaults that fill in what the caller did not supply. The order you make them in matters, because a column's type constrains what constraints can say about it.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL UNIQUE,
  country VARCHAR(60) NOT NULL DEFAULT 'GB',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  CONSTRAINT chk_email_has_at
    CHECK (instr(email, '@') > 0)
);

Six decisions in five lines. The id is the primary key, so it is NOT NULL and unique by definition. The name is required. The email is required and unique, because one account per address is a business rule rather than an application concern. The country has a default so that callers do not have to supply it. The timestamp has a default expression that is evaluated per row. And the CHECK constraint enforces a rule that a type declaration cannot express.

The design principle underneath all of it is that the database should own the invariants. An application-level check can be bypassed by a migration script, a debugging session or a second service; a constraint cannot. If the rule is that an order always belongs to a customer, that belongs in a FOREIGN KEY, not in a controller.

Two things to avoid. Do not add a constraint for a rule you do not actually mean — a UNIQUE column that the application treats as optional causes real grief when two rows legitimately share the value. And do not skip the NOT NULL declarations because the column "is always supplied": the day somebody inserts a row without it, you want the database to stop them rather than to store a NULL that breaks every downstream query.

A product table needs a key, a natural identifier that must be unique, a name, a price that cannot be negative, and a stock count that cannot be negative either. Naming the constraints makes the error messages readable when something goes wrong.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku VARCHAR(40) NOT NULL,
  name VARCHAR(120) NOT NULL,
  category VARCHAR(60) NOT NULL,
  price_cents INTEGER NOT NULL,
  stock_qty INTEGER NOT NULL DEFAULT 0,
  is_active INTEGER NOT NULL DEFAULT 1,
  CONSTRAINT uq_products_sku UNIQUE (sku),
  CONSTRAINT chk_products_price CHECK (price_cents >= 0),
  CONSTRAINT chk_products_stock CHECK (stock_qty >= 0)
);

INSERT INTO products (id, sku, name, category, price_cents, stock_qty) VALUES
  (1, 'KB-01',  'Mechanical Keyboard', 'peripherals', 7950, 24),
  (2, 'HUB-02', 'USB-C Hub',           'peripherals', 3200, 61),
  (3, 'MON-03', '27-inch Monitor',    'displays',    24999, 7);

SELECT id, sku, name, price_cents, stock_qty FROM products ORDER BY id;

Three rows come back. The price is stored in cents as an integer so that arithmetic on it is exact, the stock defaults to zero for a product that has not been counted, and the CHECK constraints mean a negative price or a negative stock count cannot be stored at all.

Each constraint has a job. The UNIQUE on sku enforces the business rule that a product code identifies exactly one product, and it also creates the index that the lookup by code will use. The two CHECK constraints turn what would otherwise be a data-quality report into an insert-time error. And the defaults keep the NOT NULL declaration honest: the columns that genuinely have a sensible fallback have one, and the ones that do not do not.

One decision worth defending in a review: the boolean column is an INTEGER with a default of 1 rather than a BOOLEAN. SQLite has no boolean type and stores it as an integer; MySQL's BOOLEAN is a synonym for TINYINT(1); PostgreSQL has a real boolean type. Using 0 and 1 everywhere keeps the same schema portable, and it is worth writing the allowed values in a CHECK constraint if you want the database to reject anything else.

Work through them in that order, because each decision constrains the next.

The primary key. Start from the business identifier — the SKU, the email, the order number — because that is the thing other records will reference and the thing people will say out loud on the phone. If it is genuinely unique and stable, use it as the primary key. If it is mutable, can be reused, or might not exist yet, use a surrogate key instead and enforce the business identifier with a separate UNIQUE constraint. The decision about whether to use a natural key has security and privacy consequences too: an email address as a primary key leaks personal data into every log, every index and every replica.

The types. Pick the narrowest type that holds the whole range of values, because a narrow type is smaller on disk, faster to compare and able to use a smaller index. Use an integer for counts and money-in-cents, a fixed-length decimal when the value genuinely needs fractional arithmetic, and text for identifiers you do not control. Do not use a float for money, and do not use a text column for something that is always a number — sorting '10' before '9' will eventually cause a bug.

The constraints. For every column, ask what an invalid value would do downstream. If it would break a report, add a CHECK. If it must exist exactly once, add a UNIQUE. If it points at another table, add a FOREIGN KEY and decide the delete behaviour deliberately. If it should never be blank, add NOT NULL — and where there is an obvious default, add one so the caller is not forced to supply it.

Finally, think about the constraints you are deliberately not adding. Every unique index costs write throughput and disk. Every CHECK costs a little on every write. A foreign key to a table with millions of rows has a real cost on both sides of the relationship. The goal is not maximum constraint, it is that every rule the business actually relies on is enforced by the database rather than by hope.