You know how to query data that's already sitting there. Now it's time to build the tables that hold it. Making a table is a lot like designing a form. You decide what info to collect, what format it should come in and which fields somebody HAS to fill out.
The CREATE TABLE Statement
Here's the basic shape of creating a table.
Example
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
age INTEGER,
created_at TIMESTAMP DEFAULT NOW()
);
Let's walk through it.
CREATE TABLE users makes a brand new table named "users"
Everything inside the parentheses defines the columns
Each column gets a name, a data type and some optional constraints
Common Data Types
Every column needs a data type, which tells the database what kind of data to expect in it.
Constraints are rules that keep your data clean and consistent. I grew up working construction with my dad, and his rule was "do it right or don't do it at all." Constraints are that rule written into your database... the table flat out refuses sloppy data.
PRIMARY KEY
Every table needs a primary key, which is a column that uniquely identifies each row.
Example
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
The id column holds a unique value for every product. Two products can never share the same ID.
NOT NULL
Requires a value other than NULL. An empty text string still passes NOT NULL, so validate blank names in your app or add a separate database check.
Example
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL, -- name is required
bio TEXT -- bio is optional (can be NULL)
);
UNIQUE
Makes sure no two rows have the same value in that column.
Example
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE -- no duplicate emails allowed
);
DEFAULT
Fills in a value automatically when you don't give it one.
Example
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
published BOOLEAN DEFAULT FALSE, -- starts as unpublished
created_at TIMESTAMP DEFAULT NOW() -- auto-sets to current time
);
Auto-Incrementing IDs
In PostgreSQL you can use SERIAL or GENERATED ALWAYS AS IDENTITY to hand out unique IDs automatically.
Example
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- auto-increments: 1, 2, 3, 4...
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
Now you don't have to pass in the id when you insert something. The database takes care of it for you.
A Real-World Example
Let's design a table for a blog app.
Example
CREATE TABLE blog_posts (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
slug VARCHAR(200) NOT NULL UNIQUE,
body TEXT NOT NULL,
author_id INTEGER NOT NULL,
published BOOLEAN DEFAULT FALSE,
view_count INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
Here's what that table gives you.
An auto-incrementing ID
A title with a max length of 200 characters
A URL-friendly slug that has to be unique
The post body with no length limit
A reference to the author (we'll get into relationships soon!)
Sensible defaults for published status, view count and timestamps
Modifying Tables with ALTER TABLE
Made a mistake, or need to add a column later on? That's what ALTER TABLE is for.
Example
-- Add a new column
ALTER TABLE users ADD COLUMN phone TEXT;
-- Remove a column
ALTER TABLE users DROP COLUMN phone;
-- Rename a column
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Add a constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
Deleting Tables with DROP TABLE
This one removes a table completely (careful, it deletes ALL the data inside it too).
Example
-- Delete the table
DROP TABLE blog_posts;
-- Delete only if it exists (prevents errors)
DROP TABLE IF EXISTS blog_posts;
TL;DR
CREATE TABLE defines a new table along with its columns and their data types
Common data types include INTEGER, TEXT, BOOLEAN, TIMESTAMP and VARCHAR
PRIMARY KEY uniquely identifies each row, and NOT NULL blocks NULL values
UNIQUE blocks duplicate values, and DEFAULT fills in values automatically
Use SERIAL or GENERATED ALWAYS AS IDENTITY for auto-incrementing IDs
ALTER TABLE changes existing tables, and DROP TABLE deletes them completely
What's Next?
You've built tables and laid out their structure. Next up you'll fill them with data using INSERT, change existing data with UPDATE and remove data with DELETE. Those three (plus SELECT) are the core of pretty much everything you'll ever do with a database.
