Creating Tables

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.

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.

Essential Constraints#

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.

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#

Stops a column from being left empty.

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.

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.

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.

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.

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.

-- 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).

-- 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 empty 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.