Part 08: Designing Tables, Data Types and Constraints

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.

Comments

[Mohamed Idris]

Part 08 Practice: Data Types and Constraints

Now you design the tables. These use our shared schema: users, orders, products, order_items. Try writing each one before peeking. Think of every column as a field on a sign-up form: what input type, and what validation?

---

1. A simple table

Write a CREATE TABLE products with: an auto-incrementing id primary key, a required name (max 100 chars), and an optional category (max 50 chars). Use Postgres style.

Solution

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

Why: SERIAL PRIMARY KEY auto-numbers each row. NOT NULL makes name required. Leaving category plain makes it optional.

---

2. Pick the right type for money

Add a price column to the products table above. It is money, must be required, and can never be negative.

Solution

price NUMERIC(10, 2) NOT NULL CHECK (price >= 0)

Why: NUMERIC stores money exactly, unlike FLOAT which drifts (remember 0.1 + 0.2). CHECK (price >= 0) lets the database itself block a negative price.

---

3. Spot the bad type choices

What is wrong with this table? Name two problems and fix them.

CREATE TABLE orders (
  id         INTEGER,
  total      FLOAT,
  created_at VARCHAR(30)
);

Solution

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,        -- was: plain INTEGER, no key, no auto number
  total      NUMERIC(10, 2),            -- was: FLOAT, bad for money
  created_at TIMESTAMP DEFAULT now()    -- was: VARCHAR, dates should be real timestamps
);

Why: FLOAT for money causes rounding errors, VARCHAR for a date breaks sorting and date math, and the original id was neither a primary key nor auto-incrementing.

---

4. Add the rules (constraints)

Write a users table where: id is the primary key, name is required, email is required and unique, and created_at defaults to the current time.

Solution

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

Why: UNIQUE on email means no two users can share one (the database guarantees it). DEFAULT now() fills the timestamp automatically, so you never set it by hand.

---

5. A default plus a check

Write an orders table where status defaults to 'pending' and total defaults to 0 but can never be negative. Keep id as the primary key.

Solution

CREATE TABLE orders (
  id     SERIAL PRIMARY KEY,
  total  NUMERIC(10, 2) NOT NULL DEFAULT 0 CHECK (total >= 0),
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
);

Why: DEFAULT fills a value when you do not provide one, so new orders start as 'pending' with total 0. CHECK (total >= 0) blocks bad data at the source.

---

6. Connect two tables (foreign key)

Add a user_id to the orders table that must point to a real row in users. An order should never be allowed to have no user.

Solution

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'
);

Why: REFERENCES users(id) is a foreign key. The database refuses any order whose user_id does not match a real user, so you never get orphan orders. NOT NULL means an order must always have a user.

---

Nice work. You can now design a table that holds the right kinds of data and refuses bad data on its own. That is a real superpower, your app gets safer for free. We go deeper into how tables connect in Part 09. See you there.