104 — Creating Tables and Constraints

Beginner

Design and build your own database tables from scratch. This SQLite-focused lesson teaches columns, constraints (PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY), ALTER TABLE changes, and safe table removal with DROP TABLE.

Learning Objectives

Create tables with CREATE TABLE and appropriate column types

Define primary keys with INTEGER PRIMARY KEY AUTOINCREMENT

Enforce required fields with NOT NULL

Set sensible defaults with DEFAULT

Prevent duplicates with UNIQUE

Validate data with CHECK constraints

Link tables together with FOREIGN KEY

Modify existing tables with ALTER TABLE

Remove tables safely with DROP TABLE IF EXISTS

Recognize constraints as business rules that keep bad data out

Step 1

CREATE TABLE — your first table from scratch

Build a new table by defining its name, columns, and data types. This is where database design begins — deciding what data to store and how to structure it.

Commands to Run

sqlite3 ecommerce.db

.headers on

.mode column

CREATE TABLE wishlists (id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id INTEGER NOT NULL, product_id INTEGER NOT NULL, added_date TEXT NOT NULL DEFAULT (date('now')), note TEXT);

.schema wishlists

What This Does

CREATE TABLE defines a new table with a name and a list of columns. Each column has a name and a type (INTEGER, TEXT, REAL). This wishlists table will let customers save products they want to buy later. The column definitions read naturally: `id` is an auto-incrementing integer primary key, `customer_id` and `product_id` are required integers, `added_date` defaults to today, and `note` is optional text. Think of CREATE TABLE as designing a spreadsheet's column headers and rules before entering any data.

Expected Outcome

The CREATE TABLE command completes silently. The .schema wishlists command displays the full table definition, confirming all columns, types, and constraints are in place.

Pro Tips

1
Table and column names should be lowercase with underscores: wishlists, customer_id (not Wishlists, CustomerID)
2
Plan your columns before writing CREATE TABLE — adding columns later with ALTER TABLE has limitations in SQLite
3
Always include a PRIMARY KEY column — it uniquely identifies each row

Common Mistakes to Avoid

⚠️Creating a table that already exists — you'll get an error. Use CREATE TABLE IF NOT EXISTS to be safe
⚠️Forgetting the data type — while SQLite doesn't strictly require it, always declare types for clarity

Was this step helpful?