Alright, let’s get our hands dirty and build a table. Think of a CREATE TABLE statement as the architectural blueprint for your data. It’s where you lay down the law: what data you’ll store, what it’s allowed to look like, and what happens when someone tries to break the rules. Screw this up, and you’re building on a foundation of digital quicksand.

We’ll start with the core of any table: the column definitions. This is where you name your data, give it a type, and tell it how to behave when it’s feeling indecisive.

The Absolute Basics: Name, Type, and the NOT NULL Life

At its simplest, a column definition is a name followed by a data type. But if you stop there, you’re asking for trouble. The single most important thing you can add is NOT NULL. Let me be direct: you should default to NOT NULL for every column unless you have a damned good reason to allow NULL. NULL is not your friend; it’s a three-state-logic nightmare that complicates every query you’ll ever write. It doesn’t mean “zero” or “empty string”—it means “this value is unknown.” And unknown values have a nasty habit of disappearing from results when you least expect it.

Here’s a decent start for a users table. See how I’m being explicit? No defaults, no room for ambiguity.

CREATE TABLE users (
    user_id integer NOT NULL,
    username varchar(50) NOT NULL,
    first_name varchar(100) NOT NULL,
    last_name varchar(100) NOT NULL,
    date_joined timestamp NOT NULL
);

This is a solid, no-nonsense foundation. Every row must have a value for these columns. The database will simply reject any attempt to insert a row without them. This is a good thing. It protects you from your own forgetful future self.

Defaults: For When You’re Feeling Lazy (or Smart)

Sometimes, you don’t want to force a value to be provided on every INSERT. Maybe it’s something the database can figure out for itself. This is where DEFAULT comes in. It’s your way of saying, “If you don’t tell me what this should be, I’ll use this.”

The classic example is a created_at timestamp. You almost always want it to be the exact moment the row is inserted. Manually passing NOW() is error-prone and, frankly, a waste of keystrokes. Let the database handle it.

CREATE TABLE posts (
    post_id integer NOT NULL,
    title text NOT NULL,
    body text NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_published boolean NOT NULL DEFAULT false
);

Now, when I insert a new post, I can omit created_at and is_published, and the database will do the right thing:

INSERT INTO posts (post_id, title, body) VALUES (1, 'My First Post', 'A thrilling tale of SQL...');
-- created_at is automatically set to the transaction time
-- is_published is automatically set to false

This is brilliant for audit columns (created_at, created_by) and for setting sensible, safe initial states (like is_published defaulting to false instead of NULL).

The NULL Trap and How to Avoid It

Let’s talk about the scenario I warned you about. Look at this common, well-intentioned mistake:

CREATE TABLE user_profiles (
    profile_id integer NOT NULL,
    user_id integer NOT NULL,
    bio text, -- This is NULLable. Why? Do you want an empty bio or an unknown bio?
    website_url varchar(255) -- This is also NULLable. Bad.
);

The designer here thought, “Well, they might not have a bio or a website, so I’ll just leave it NULL.” This is a classic error. It creates a mess. Does bio IS NULL mean they haven’t written one yet, or that they explicitly want their bio to be blank? You can’t tell.

The solution is almost always to use NOT NULL combined with a sensible DEFAULT that represents the “empty” state. For text, that’s an empty string ''. For numbers, it might be 0. This makes your data consistent and your queries infinitely simpler.

CREATE TABLE user_profiles (
    profile_id integer NOT NULL,
    user_id integer NOT NULL,
    bio text NOT NULL DEFAULT '', -- Now it's clear: no bio is an empty string.
    website_url varchar(255) NOT NULL DEFAULT '' -- Ditto.
);

Now, when you query for profiles with no website, you look for website_url = '', a clear, unambiguous condition. You’ve eliminated an entire class of potential bugs related to NULL handling. You’re welcome.

A Quick Note on Naming and Quoting

Stick to lowercase names, use underscores, and for the love of all that is holy, avoid quoted identifiers. created_at is fine. "createdAt" is a maintenance nightmare that will force you to double-quote the name in every query forever. Don’t do it. The SQL standard says unquoted identifiers are folded to lowercase anyway, so Created_At becomes created_at. Pick a simple, clear standard and stick with it. Your future self, who is trying to remember if that column was UserID, user_id, or userId, will thank you profusely.