Skip to main content

Tables in SQLite3

If you've ever looked at a CREATE TABLE statement and felt your eyes glaze over, this guide is for you. No assumed knowledge, no fancy database-speak — just a plain-English walkthrough of how to build a table in SQLite3.

First, What Even Is a Table?

Think of a table like a spreadsheet. It has rows (each row is one "thing," like one person or one order) and columns (each column is one piece of information about that thing, like a name or a price). Creating a table just means telling SQLite: "Here's what I want to store, and here's what each piece of information looks like."

The Basic Recipe

Every table you create follows the same basic pattern:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints
);

Break that down:

  • table_name — whatever you want to call your table (like users or products)
  • Each line inside the parentheses is one column
  • datatype tells SQLite what kind of information goes in that column (a number? some text?)
  • constraints are optional rules, like "this can't be empty"

One handy quirk of SQLite: it's a bit relaxed about data types. Most databases will yell at you if you try to put text into a number column. SQLite mostly lets it slide. That said, it's still smart to stick to the type you declared — future-you will thank present-you.

The Data Types You'll Actually Use

SQLite keeps things simple with just five data types:

  • INTEGER — whole numbers (1, 42, -7)
  • TEXT — words, sentences, anything text-based
  • REAL — numbers with decimals (19.99, 3.14)
  • BLOB — raw data, like a file or an image (you won't need this often as a beginner)
  • NULL — means "nothing here"

A Real Example You Can Actually Use

Let's build a table for storing users. Here's what it might look like:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER,
    salary REAL,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Let's translate each part into plain English:

  • id INTEGER PRIMARY KEY AUTOINCREMENT — SQLite automatically assigns each new row its own unique number, starting at 1 and counting up. You never have to think about it again.
  • username TEXT NOT NULL UNIQUE — this field is required (NOT NULL) and no two users can share the same one (UNIQUE).
  • email TEXT NOT NULL — also required, but duplicates are allowed here (no UNIQUE).
  • age INTEGER — just a plain number, totally optional.
  • salary REAL — a number that can have decimals.
  • is_active INTEGER DEFAULT 1 — if you don't specify a value, SQLite fills in 1 automatically.
  • created_at TEXT DEFAULT CURRENT_TIMESTAMP — SQLite stamps the current date and time on its own, no extra work needed.

The Rules You Can Attach to a Column (Constraints)

Constraints are just rules that keep your data honest. Here's the lineup:

  • PRIMARY KEY — the column that uniquely identifies each row (like a social security number for that row)
  • NOT NULL — this field can't be left blank
  • UNIQUE — no two rows can have the same value here
  • DEFAULT — if nothing is provided, use this value instead
  • CHECK — only accept values that pass a specific test
  • FOREIGN KEY — links this table to another table (more on that below)

Here's CHECK in action — it stops bad data before it ever gets in:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK(price > 0),
    category TEXT DEFAULT 'general'
);

That CHECK(price > 0) simply means: "Refuse to save this row if someone tries to set a negative or zero price." It's a built-in bouncer for your data.

Connecting Tables Together

Real databases usually have more than one table, and those tables often need to talk to each other. Say you have a users table and an orders table — you'd want each order to know which user it belongs to. That's what FOREIGN KEY does:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    order_date TEXT DEFAULT CURRENT_TIMESTAMP,
    total REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

This tells SQLite: "The user_id in this table should match an id that actually exists in the users table." It's a safety net that stops you from accidentally linking an order to a user who doesn't exist.

One important catch: SQLite doesn't enforce this rule by default. You have to explicitly turn it on with:

PRAGMA foreign_keys = ON;

Easy to forget, but important — without it, SQLite won't actually check that the link makes sense.

A Couple of Handy Shortcuts

Don't want an error if the table's already there? Add IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY,
    message TEXT,
    timestamp TEXT DEFAULT CURRENT_TIMESTAMP
);

This just tells SQLite: "Create this table, but if it already exists, don't throw a fit — just move on."

Need a table that disappears when you're done? Use TEMPORARY:

CREATE TEMPORARY TABLE temp_calculations (
    id INTEGER,
    result REAL
);

This is useful for scratch work — the table vanishes automatically once your session ends.

Build a Table and Fill It in One Go

Want to skip a step? You can create a table and load it with data from another table at the same time:

CREATE TABLE active_users AS 
SELECT * FROM users WHERE is_active = 1;

This grabs every user marked as active and copies them straight into a brand-new table, no separate insert step needed.

A Few Habits Worth Building Early

  • Always give your table a PRIMARY KEY — it makes finding and organizing rows much faster.
  • Use column names that actually describe what's inside them (email beats col3 every time).
  • Pick sensible data types, even though SQLite won't force you to.
  • Mark required fields as NOT NULL so you don't end up with a bunch of empty gaps later.
  • If you'll be searching a column a lot, consider adding an index to speed things up.

Trying It Yourself on the Command Line

Open up the terminal and try this:

sqlite3 mydatabase.db
sqlite> CREATE TABLE contacts (
   ...>     id INTEGER PRIMARY KEY,
   ...>     name TEXT NOT NULL,
   ...>     phone TEXT UNIQUE
   ...> );
sqlite> .schema contacts

That .schema contacts command is your friend — it prints out exactly how the table is structured, so you can double check everything looks right. If you want to see every table in your database at once, just type .tables.

Wrapping Up

Creating a table in SQLite comes down to a simple question: what am I storing, and what rules should it follow? Once you've got the basic syntax down — column names, data types, and a few constraints — you're equipped to build database structures that are clean, reliable, and ready for real use.

Labels:

Popular posts from this blog

How to Check if Someone is Connected to Your Machine in Linux

Picture this: you glance at your system monitor and notice your CPU is humming along even though you're not running anything demanding. Or maybe your internet feels sluggish for no obvious reason. A small, uneasy thought creeps in — is someone else on my machine right now? For Linux users, this isn't something you have to wonder about. Linux ships with a powerful set of built-in tools that let you see exactly who's connected, who's logged in, and what your network is doing at any given moment. You don't need to be a security expert to use them — you just need to know where to look. This guide walks you through the practical, no-nonsense steps to check for unauthorized connections on your Linux system, with real commands you can run right now. Why Monitoring Network Connections Matters Every device on a network — including your own Linux machine — communicates using an IP address. When another device or user connects to your system, that connection shows up as a trac...
Read more

How to Set Up a Linux Web Server and Host an HTML Page Easily

Setting up a web server on Linux means spending a fair amount of time in the terminal — Linux leans heavily on the command line rather than clicking through menus, so you'll be typing out instructions more often than not.  If you're new to this, it can feel a little intimidating at first, but the good news is you don't need to become a Linux wizard overnight. A handful of core commands will get you surprisingly far. A few you'll lean on constantly: cd — move between directories ls — see what's in the current directory mkdir — create a new folder nano or vim — edit files right there in the terminal sudo — run something with administrator privileges Get comfortable with these and you'll be able to navigate around, tweak configuration files, and install software without much trouble. You don't need to memorize everything — you just need to be confident enough to follow along with clear instructions, which is exactly what this guide aims to give you....
Read more

Linux Network Troubleshooting

If you've spent any time as a sysadmin — or honestly, just as someone who's had to fix their own home network at 11pm — you know that connectivity issues are one of the most common headaches out there. The good news is that a handful of core tools and a methodical approach can take you from "why isn't this working" to a root cause pretty quickly.  This guide walks through the essentials: configuring interfaces, managing routes, and diagnosing problems when things go sideways. Configuring Network Interfaces Your network interfaces are the actual bridge between your machine and the outside world, so getting them configured correctly is step one for any kind of reliable connectivity. Doing It Manually ifconfig is the old-school, tried-and-true tool for this on Unix-like systems. To see everything currently configured, run: ifconfig -a If you need to manually set up a specific interface — assigning an IP, a netmask, and bringing it online — it looks like this: ifconf...
Read more