Part of the "SQL: Zero to Ninja" series, for junior web devs who want SQL to finally click.

Up to now, the tables already existed. You just queried them. But who decided that price holds numbers and email holds text? Who made sure two users can never share the same email? That was the person who designed the table. Today that person is you.

The idea in one line

CREATE TABLE builds a table, data types say what kind of thing each column holds, and constraints are rules the database promises to enforce so bad data can never sneak in.

The metaphor: a table is a sign-up form

Think of a table definition like a good sign-up form on a website.

• Each field has the right input type. The birthday field is a date picker, not a free text box where someone types "next Tuesday".
• Some fields are required. You cannot submit without an email.
• Some fields must be unique. You cannot sign up with an email someone already used.
• Some fields have a default. If you leave "newsletter" alone, it is set for you.

Constraints are exactly that form validation, except the database guarantees it. Even if a buggy app or a careless script tries to push in junk, the database says "no".

A column definition  =  one field on the form
A data type          =  the input type (date picker, number box, text box)
A constraint         =  the validation rule (required, unique, default)

CREATE TABLE, the shape

Here is the basic shape. Each line is one column: a name, a type, then any rules.

CREATE TABLE products (
  id       SERIAL PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  price    NUMERIC(10, 2) NOT NULL,
  category VARCHAR(50)
);

Read it top to bottom and it is almost English. Let's unpack the pieces.

Common data types (pick the right input box)

• INTEGER and BIGINT: whole numbers. Use these for counts and ids. BIGINT is just a bigger range for when you expect a lot of rows.
• VARCHAR(n) and TEXT: words. VARCHAR(100) is text with a max length of 100. TEXT is text with no fixed limit (good for long blog posts or comments).
• BOOLEAN: true or false. Perfect for is_active or email_verified.
• DATE and TIMESTAMP: points in time. DATE is just a day. TIMESTAMP is a day plus a time.
• NUMERIC (also called DECIMAL): exact numbers with decimals. This is the one for money.

The money trap (please read this one)

It is tempting to store money as FLOAT. Do not. Floats are approximate, and math on them goes weird:

-- with FLOAT, this can come out as 0.30000000000000004
SELECT 0.1 + 0.2;

That tiny error adds up across millions of orders and your totals stop matching. Use NUMERIC (or DECIMAL) for any money column. It stores the exact value.

price NUMERIC(10, 2)   -- up to 10 digits, 2 after the dot. Exact. Safe for money.

Store dates as real dates, not text

Another trap. Do not store a date as a string like '2026-05-29' in a VARCHAR. If you do, sorting breaks, comparing breaks, and 'next week' could end up in there too. Use a real DATE or TIMESTAMP. Then the database understands it is time, and you get sorting and date math for free.

Wrong:  created_at VARCHAR(20)     -- "it's just text, right?" ... pain later
Right:  created_at TIMESTAMP       -- the database knows this is a moment in time

Primary keys and auto-increment

Every row needs a way to be pointed at uniquely. That is the primary key, almost always called id. It must be unique and never empty, and the database can fill it in automatically.

• In Postgres: SERIAL (or the newer IDENTITY) auto-numbers it for you.
• In MySQL: AUTO_INCREMENT does the same job.

-- Postgres
id SERIAL PRIMARY KEY

-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY

You never type the id yourself. You insert a row, the database hands you the next number.

Constraints: rules the database keeps for you

These are the form-validation rules. Put them on a column and the database enforces them forever.

• NOT NULL: this field is required. No empty allowed.
• UNIQUE: no two rows can share this value. Classic for email.
• DEFAULT: if you do not give a value, use this one.
• CHECK: the value must pass a condition you write.

status     VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP   DEFAULT now(),
total      NUMERIC(10, 2) CHECK (total >= 0)

That CHECK (total >= 0) means an order total can never be negative. Try to insert -5 and the database refuses. You did not write any app code for that. The database just guards it.

FOREIGN KEY: no orphans allowed

An order belongs to a user. A foreign key says "this column must point to a real row in another table." If orders.user_id is a foreign key to users.id, then you cannot create an order for user 999 if no user 999 exists. The database refuses the orphan.

user_id INTEGER NOT NULL REFERENCES users(id)

This keeps your data honest. We go much deeper on relationships in Part 09, including what happens when you delete a user who still has orders.

A full example, our shared schema

Here are users and orders built for real, using everything above.

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL UNIQUE,
  country    VARCHAR(2),
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id),
  total      NUMERIC(10, 2) NOT NULL DEFAULT 0 CHECK (total >= 0),
  status     VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

Read it like a form. Email is required and unique. Total is money (NUMERIC), defaults to 0, and can never go negative. Every order must belong to a real user. Created_at fills itself in. That is a lot of safety baked right into the design.

Gotchas juniors hit

1. FLOAT for money. It looks fine in tests, then your totals drift. Use NUMERIC.
2. Dates as strings. Sorting and date math quietly break. Use DATE or TIMESTAMP.
3. No constraints, "the app will handle it". Apps have bugs. Scripts run at 2am. The database is the one wall that always holds. Add NOT NULL, UNIQUE, and CHECK at the table level.

Recap

• CREATE TABLE builds a table. Each column is a name, a type, and optional rules.
• Pick the right type: INTEGER/BIGINT for whole numbers, VARCHAR/TEXT for words, BOOLEAN, DATE/TIMESTAMP for time, NUMERIC for money (never FLOAT).
• A primary key (id) is auto-filled by SERIAL (Postgres) or AUTO_INCREMENT (MySQL).
• Constraints are form validation the database guarantees: NOT NULL, UNIQUE, DEFAULT, CHECK.
• A FOREIGN KEY stops orphan rows, like an order with no real user.

Your turn

Design a products table from the shared schema. Make id a primary key, name required, price exact money that can never be negative, and category optional. Write the CREATE TABLE. If you can say out loud why price is NUMERIC and not FLOAT, you nailed it.

Next up, Part 09: Relationships and Normalization, where those foreign keys grow into the full story of how tables connect.