Right, let’s talk about NOT NULL. It’s the simplest constraint in the book, but also one of the most important. It doesn’t add fancy logic or relationships; it just does one job: it stops NULL from getting into a column. And that, my friend, is 90% of data sanity.

Think of NULL as the database’s way of saying “I have no idea.” It’s not zero, it’s not an empty string, it’s not false. It’s the absence of a value. This is useful sometimes (like when you genuinely don’t have the data), but letting NULL run rampant through your tables is like inviting a poltergeist into your application logic. Your code will spend half its time checking IS NOT NULL before it can do anything useful. NOT NULL is your first and best line of defense against this chaos. It forces you, the human, to make a decision: “What must I have for this record to be valid?”

By default, if you don’t specify NOT NULL, the column is nullable. This is a historical artifact from the dark ages of SQL and is, frankly, a terrible default. You should be explicitly deciding which columns are optional, not which are required. The first best practice is right here: make NOT NULL your default mindset. Only allow NULL when you have a compelling, thought-out reason.

Here’s the basic syntax. It’s about as complicated as a light switch.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    date_joined TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    bio TEXT -- This one is optional, so it can be NULL
);

In this table, you can’t create a user without a username, email, and date_joined. The bio field, however, can be left as NULL for the strong, silent types.

The Primary Key / NOT NULL Symbiosis

Here’s a bit of trivia that isn’t trivial: a PRIMARY KEY constraint automatically includes a NOT NULL constraint. You cannot have a primary key that is NULL. The system enforces this for you. If you try to define one manually, it’ll complain about the redundancy.

CREATE TABLE bad_idea (
    id INT PRIMARY KEY NOT NULL -- The NOT NULL is redundant. Don't do this.
);

The database will accept this, but any seasoned developer will give you a mildly disappointed look. The PRIMARY KEY already implies NOT NULL. It’s like putting a “Do Not Eat” sign on a brick.

The DEFAULT Value Sidekick

NOT NULL and DEFAULT are best friends. Without a DEFAULT value, an INSERT statement must provide a value for every NOT NULL column. A DEFAULT lets you be lazy (which is a virtue in programming) by letting the database fill in the blanks for you with a sensible value. The most common use is for timestamps, as shown above with DEFAULT NOW(). But you can use it for anything.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    order_status VARCHAR(20) NOT NULL DEFAULT 'pending'
);

Now you can insert a record by just specifying the product_id, and the database will handle the rest.

INSERT INTO orders (product_id) VALUES (123);
-- quantity becomes 1, order_status becomes 'pending'

The One Pitfall: Adding NOT NULL to an Existing Column

This is where you can get into trouble. You can’t just ALTER TABLE an existing column to NOT NULL if there are already NULL values lurking in it. The database will rightly stop you because it doesn’t know what magic value to use for all those existing invalid records. You have to clean up your mess first.

  1. Find the NULLs and update them to a value.
  2. Or, add a DEFAULT value for future inserts.
  3. Then add the NOT NULL constraint.

Trying to do it in one go will get you an error.

-- This will FAIL if any NULLs exist in the 'username' column
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- Do this instead:
-- First, update any NULL values to a placeholder
UPDATE users SET username = 'unknown_legacy_user' WHERE username IS NULL;

-- Then, set the column to NOT NULL
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

Always run a check (SELECT COUNT(*) FROM table WHERE column IS NULL;) before attempting the ALTER TABLE. It saves you from embarrassing execution errors.

In summary, use NOT NULL aggressively. It’s not a suggestion; it’s a declaration of intent about what your data should look like. It makes your data more predictable, your queries simpler, and your life easier. It’s the foundation everything else is built on. Don’t build on a foundation of “maybe.”