How do you design a CREATE TABLE statement — what actually belongs in it?
A create table statement has four parts, and the last two are the ones that people leave out.
CREATE TABLE customers (
id INTEGER PRIMARY KEY, -- the key
name VARCHAR(120) NOT NULL, -- the type and the rules
email VARCHAR(200) NOT NULL UNIQUE, -- the rules again
country VARCHAR(60) NOT NULL DEFAULT 'Unknown',
created_on TEXT NOT NULL DEFAULT '2026-01-01',
total_spend_cents INTEGER NOT NULL DEFAULT 0
);
The four parts are these. The name of the table. The columns, each with a type. The rules on each column, which are the NOT NULL, the UNIQUE, the DEFAULT and the CHECK. And the table level rules, which are the primary key and the foreign keys.
So what actually belongs in it? Everything that you would otherwise have to remember in your program. The rule that the email is not empty and is different for every customer belongs in the table, because then no code can break it. The same goes for the amount, the dates and the links to other tables.
There are three things that are worth adding as well, and they are not part of the standard.
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(200) NOT NULL UNIQUE,
country VARCHAR(60) NOT NULL DEFAULT 'Unknown',
total_spend_cents INTEGER NOT NULL DEFAULT 0 CHECK (total_spend_cents >= 0)
);
The first one is a comment on each column, so that anybody who opens the table later knows what it is for. The second one is a check rule, which is the database's own way of saying that a value must be sensible. The third one is an index on the columns that you search by and that you join on, because a table without the right index is slow in a way that nobody notices until it is a problem.
The next two questions in this topic ask for a real table and for the reasoning behind the choices.