NOT NULL

Question: What does NOT NULL mean in SQL?

Answer: NOT NULL is a column constraint that says a column must always have a real value; the database will reject NULL for that column. It does not mean empty string, zero, or false — those are valid values if the column type allows them. Interviewers ask this because it tests whether you understand the difference between “missing/unknown” and an actual value.

Interview-Ready Answer: I’d say NOT NULL is a constraint that prevents a column from storing NULL, so every row must have a value there. It’s important because NULL means “unknown or missing,” which is different from 0 or an empty string. In practice, the database checks this on INSERT and UPDATE, and it will reject the row if the column is null.

What NOT NULL really means

NULL is a special SQL marker for “missing,” “unknown,” or “not applicable.” A NOT NULL constraint tells the database: this column is required. Think of it like a form field marked with a red asterisk — you cannot submit the row without filling it in.

1. The database receives an INSERT or UPDATE.
2. It evaluates the value for each constrained column.
3. If the value is NULL, the engine rejects the change immediately.
4. If the value is present, the row can be written normally.

Under the hood, most engines store a small “null flag” alongside each column or row. The check itself is simple and fast, usually O(1) per row for the constraint evaluation. The real cost is not the check; it is the write to disk, index maintenance, and transaction logging that already happen for the row.

Why use it

Use NOT NULL for data that must exist for the row to make sense: user email, order total, created timestamp, product SKU, or foreign keys that are mandatory. It improves data quality because bad data is stopped at the door instead of being discovered later in reports, joins, or application code.

NOT NULL vs common alternatives

PRIMARY KEY is stricter than NOT NULL because it also requires uniqueness. A column can be NOT NULL without being unique, like a country_code column where many rows can store US.

Important edge cases

1. Empty string is not NULL. In most databases, '' is a real value, so it passes NOT NULL even though it may still be a bad business value.
2. Default values only apply when the column is omitted. If you explicitly send NULL, the default does not rescue you; the constraint still fails.
3. Adding NOT NULL later may require cleanup. If existing rows already contain nulls, the change fails until you backfill the data.
4. Some databases treat key columns specially. For example, primary key columns are implicitly not null in many systems.

Performance and design notes

NOT NULL is one of the cheapest constraints to enforce. It does not create an index, and it does not speed up lookups by itself. Its value is correctness: it narrows the possible states of your data, which makes joins, filters, and application logic safer.

Memory hook: imagine a seat with a seatbelt clip. NOT NULL says the clip must be fastened before the car can move; an empty seat is not acceptable, but a properly filled seat can be anything valid.

Version note: exact syntax for adding or dropping NOT NULL can vary by database engine. The concept is universal, but the migration path may differ, especially when existing nulls must be cleaned up first.

Real-world story: checkout systems

In an e-commerce checkout service, an orders table usually needs a non-null customer_id, order_total, and created_at. Those fields are essential because every order must belong to a customer and must have a price and time.

Imagine a bug in the API layer where a retry path forgets to include customer_id. If the column is NOT NULL, the database rejects the bad row immediately. Without that constraint, the row gets saved with a null customer, and later the order cannot join to the customer table, the receipt job fails, and support sees “missing customer” tickets.

What goes wrong when people misunderstand it: a developer assumes the app layer validation is enough and removes the database constraint. A release goes out, a null sneaks in, and suddenly analytics show broken revenue numbers because the warehouse ETL cannot group orders by customer. The logs often look like “cannot join on null key” or “order.customer_id is null,” and the user impact is delayed emails, broken dashboards, and manual cleanup work.

Follow-up & Tricky Questions:

• Can a column be both NOT NULL and UNIQUE? Yes. That means the value is required and cannot repeat, which is common for email addresses or usernames.
• What is the difference between DEFAULT and NOT NULL? DEFAULT supplies a value when one is omitted; NOT NULL blocks missing values entirely. They are often used together so the column is always filled.
• Can I add NOT NULL to an existing column? Yes, but only after fixing any rows that currently contain NULL. Many databases will scan the table and refuse the change until the data is clean.
• Does NOT NULL improve query speed? Not by itself. It improves correctness; any performance benefit is indirect because the optimizer knows the column cannot be null and can simplify some logic.
• Should foreign keys always be NOT NULL? Not always. If a relationship is optional, the foreign key can be null; if every row must point to a parent, then make it NOT NULL.
• Tricky: Is an empty string the same as NULL? No. Empty string is a real value, while NULL means unknown or missing. This is one of the most common interview traps.
• Tricky: Does a primary key need an extra NOT NULL? Usually no, because primary keys are already not null by definition in standard SQL and in mainstream engines.
• Tricky: If I insert without the column, will NOT NULL fail even when a default exists? No, not if the default fills it. But if you explicitly send NULL, the default does not apply and the insert fails.

Common Mistakes:

• Thinking empty string equals null. Correction: '' is a value; NULL is the absence of a value.
• Relying only on application validation. Correction: enforce critical rules in the database too, because bugs and backfills bypass app code.
• Expecting DEFAULT to save explicit NULL. Correction: defaults are used when the column is omitted, not when null is passed.
• Forgetting existing data when adding the constraint. Correction: backfill or clean nulls first, then apply NOT NULL.

Memory Hook: “Required field at the database door.” If the value is missing, the bouncer sends the row away; if it is present, the row gets in.

Cheat Sheet:

• NOT NULL means the column must contain a value.
• NULL means unknown or missing, not zero or empty string.
• The database checks it on INSERT and UPDATE.
• DEFAULT helps when a column is omitted, not when explicit NULL is sent.
• PRIMARY KEY implies uniqueness and not null.
• Use it for required business data like IDs, totals, and timestamps.

Practice Tasks:

• Create a table with one required column and one optional column, then try inserting rows with and without values.
• Add a DEFAULT to a NOT NULL column and observe the difference between omitting the column and sending NULL.
• Take a table with nulls, clean the data, and then add a NOT NULL constraint.