NOT NULL Constraint in SQL
Introduction
The SQL NOT NULL constraint requires every stored row to contain a non-NULL value in a column. It is one of the simplest database constraints, but it expresses an important data-model decision: this attribute or relationship must always be present. When the rule is correct, NOT NULL prevents incomplete records regardless of which application, API, import, script, or user writes to the table.
CREATE TABLE employees (
employee_id INTEGER NOT NULL,
employee_name VARCHAR(100) NOT NULL
);
Both columns require values. This does not mean every value is valid, unique, or meaningful; it means only that SQL NULL is prohibited. Good design combines nullability with types, keys, checks, foreign keys, defaults, and application validation to represent the complete business rule.
What SQL NULL Means
NULL represents the absence of a known stored value. Depending on context, it may mean unknown, missing, not supplied, not yet determined, or not applicable. Those meanings are not always interchangeable, so column documentation and workflow design should explain which interpretation is intended.
NULL is not zero. A zero bonus says the known amount is zero; a NULL bonus may say the amount has not been decided. NULL is also not an empty string. An empty string is a known character value with zero characters, although Oracle traditionally treats empty character strings as NULL in SQL. Product-specific behavior matters when applications must support several databases.
Why NOT NULL Exists
Without a constraint, a database may accept an employee with no name or an order with no creation time. If the business cannot use such a row, the schema is too permissive. NOT NULL moves the requirement from documentation into the database boundary.
CREATE TABLE employees (
employee_id INTEGER NOT NULL,
employee_name VARCHAR(100) NOT NULL
);
-- Rejected because employee_name would be NULL
INSERT INTO employees (employee_id)
VALUES (101);
The resulting error is useful: it stops invalid data near its source rather than allowing downstream reports, joins, and business processes to fail unpredictably.
Basic Syntax and Column Choices
Place NOT NULL after the column data type. Omit it when NULL is a legitimate state. Production definitions should be explicit rather than relying on DBMS settings or tool defaults.
CREATE TABLE customers (
customer_id BIGINT NOT NULL,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(254) NOT NULL,
middle_name VARCHAR(100),
phone VARCHAR(30),
created_at TIMESTAMP NOT NULL
);
Identity, name, email, and creation time are mandatory under this model. Middle name and phone are optional. The correct choice comes from domain requirements, not from a rule that every column should look strict.
Think in Terms of Mandatory Attributes
Ask whether a valid row can exist without the attribute at every relevant lifecycle stage. An order requires an identity and creation time. A delivered timestamp cannot exist before delivery, so it usually remains nullable. A customer's phone may be optional even though it is valuable for contact.
NOT NULL is appropriate when absence would make the row structurally invalid. A nullable column is appropriate when absence itself is honest, meaningful information. The design should not force fake values merely to satisfy an unnecessarily strict schema.
Application Validation and Defense in Depth
Forms and APIs should validate required fields so users receive clear messages. That is not sufficient protection because data may also arrive through ETL, scheduled jobs, database clients, migrations, integrations, and future services. Every writer would have to reproduce the rule perfectly.
Use application validation for usability and database NOT NULL for final integrity. The two layers are complementary. A constraint violation should be translated into a useful application response while remaining visible in monitoring so unexpected writers or deployment mismatches can be corrected.
Valid Inserts, Explicit NULL, and Omitted Columns
A valid INSERT supplies all required values. Explicitly assigning NULL to a NOT NULL column fails. Omitting the column also fails when the database has no generated or default value to supply.
INSERT INTO employees (employee_id, employee_name)
VALUES (101, 'John');
-- Rejected
INSERT INTO employees (employee_id, employee_name)
VALUES (102, NULL);
-- Also rejected when no default exists
INSERT INTO employees (employee_id)
VALUES (103);
These behaviors make the table contract predictable. Clients should list INSERT columns explicitly so schema changes and default behavior are visible.
NOT NULL and DEFAULT Solve Different Problems
A DEFAULT supplies a value when a column is omitted in an applicable INSERT. NOT NULL determines whether an explicit or resulting NULL may be stored. A default alone does not necessarily make the column non-nullable.
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
This definition expresses two rules: new rows normally begin as ACTIVE, and status may never be NULL. Use both only when both statements are true. A fake default such as an arbitrary country or birth date silently creates incorrect data and is worse than an honest NULL.
NOT NULL, UNIQUE, and PRIMARY KEY
NOT NULL requires a value but permits duplicates. UNIQUE protects business uniqueness but has DBMS-sensitive behavior when NULL is allowed. A PRIMARY KEY provides row identity and inherently prevents NULL in its participating columns.
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
email VARCHAR(254) NOT NULL UNIQUE,
display_name VARCHAR(100) NOT NULL
);
Here the primary key identifies rows, while email is both required and unique. Writing NOT NULL again on the primary key is normally redundant. Choose each constraint for its own responsibility rather than treating them as interchangeable syntax.
NOT NULL and CHECK Work Together
NOT NULL does not validate range or domain. A non-null quantity can still be negative, and a non-null status can still contain an unsupported value. CHECK constraints add those rules.
quantity INTEGER NOT NULL CHECK (quantity > 0),
price DECIMAL(12,2) NOT NULL CHECK (price >= 0),
status VARCHAR(20) NOT NULL
CHECK (status IN ('ACTIVE','INACTIVE'))
This combination is especially important because SQL uses three-valued logic. A CHECK condition that evaluates to UNKNOWN because of NULL may be accepted by many databases. Add NOT NULL when presence is independently required rather than assuming the CHECK rejects absence.
Mandatory and Optional Foreign Keys
A foreign key ensures that a non-NULL value references an existing parent, but it does not automatically require the relationship. Combining it with NOT NULL makes participation mandatory from the child row's perspective.
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
A nullable self-reference such as employees.manager_id can represent an employee who currently has no manager. If a manager value is supplied, the foreign key still validates it. Nullability therefore communicates relationship cardinality and lifecycle.
Required Numbers and Monetary Values
A numeric NOT NULL column can store zero and negative numbers unless additional rules prohibit them. For order items, quantity might need to be greater than zero. A monetary amount may allow zero in one context and require a positive value in another.
Choose exact decimal types for financial values, define currency, and add CHECK constraints aligned with the business meaning. NOT NULL answers whether the amount exists; precision, scale, range, and cross-column rules answer whether it is usable.
Required Strings Do Not Prevent Blank Text
NOT NULL prevents SQL NULL, not an empty string or whitespace-only text on most databases. A required name column may still accept '' or spaces unless the application and database add appropriate validation.
name VARCHAR(100) NOT NULL
CHECK (LENGTH(TRIM(name)) > 0)
Function names and empty-string behavior vary by DBMS, so test the chosen rule. Unicode whitespace, normalization, and application trimming may also matter. Do not claim that NOT NULL alone guarantees meaningful text.
Boolean Columns and Three States
A nullable Boolean has three states: true, false, and unknown. This can be useful when a fact has not yet been determined, but it can also complicate queries and application mappings. If every account must have a known enabled state, use NOT NULL with a semantically correct default.
Do not make every Boolean NOT NULL automatically. For a medical answer or pending verification, unknown may be distinct from false. If the domain has more than two meaningful states, an explicit status may communicate better than a nullable Boolean.
Workflow Timestamps Are Often Nullable
created_at commonly exists for every persisted row and can be NOT NULL with a timestamp default. Event timestamps such as approved_at, shipped_at, and delivered_at cannot have values before their events happen, so NULL can intentionally represent "not yet occurred."
This use of NULL is valuable, but pair it with status or cross-column checks when consistency matters. A delivered order should normally have a delivered timestamp, while a pending order should not. NOT NULL is unconditional; conditional presence requires a richer rule.
Conditional Requirements Need CHECK Logic
Some values are required only in a specific state. A cancellation reason may be mandatory when status is CANCELLED but absent otherwise. Declaring the column NOT NULL would incorrectly require it for every row.
CHECK (
(status = 'CANCELLED' AND cancellation_reason IS NOT NULL)
OR (status <> 'CANCELLED' AND cancellation_reason IS NULL)
)
Adapt the condition to NULL status behavior and DBMS capabilities. Conditional requirements are workflow constraints, while NOT NULL represents an unconditional structural requirement.
SQL NULL and JSON null Are Different
A JSON column can be SQL NULL, meaning no document is stored. A non-NULL JSON document can itself contain the JSON literal null or omit a key. These states are distinct and may be queried differently.
Declaring a JSON column NOT NULL guarantees only that a document value exists. It does not guarantee required keys, valid domain values, or absence of JSON nulls. Use JSON schema checks, generated columns, product-specific validation, or relational columns for core facts that need strong constraints.
NULL and Three-Valued Query Logic
Comparisons with NULL do not evaluate to true or false; they usually evaluate to UNKNOWN. Use IS NULL and IS NOT NULL, not = NULL. Boolean combinations involving UNKNOWN can surprise developers who assume two-valued logic.
SELECT *
FROM employees
WHERE manager_id IS NULL;
NOT NULL reduces this uncertainty for the constrained column, but outer joins, expressions, CASE logic, and aggregate results can still produce NULL in query output. Table nullability does not guarantee that every selected expression is non-NULL.
Aggregate Functions and Expressions
Most aggregate functions ignore NULL inputs, while COUNT(*) counts rows and COUNT(column) counts non-NULL values. An aggregate over no matching rows can return NULL even when the source column is NOT NULL. Arithmetic and concatenation can also propagate NULL depending on the DBMS.
Views expose query results, not stored-column guarantees. A view column derived from an outer join may be nullable even when its base column is not. Consumers should understand result semantics rather than inferring them solely from base-table DDL.
Fake Values Damage Data Quality
When absence is legitimate, forcing 'UNKNOWN', 'N/A', -1, or 1900-01-01 into a NOT NULL column creates values that look real. Reports can no longer distinguish an actual value from missing information, and range calculations become misleading.
An explicit UNKNOWN member can be valid in a governed warehouse or reference domain, but that is a modeled state, not a convenient placeholder. Choose NULL, an explicit domain state, or a separate reason column according to what the business needs to know.
Unknown, Not Applicable, and Not Yet Known
One nullable column may collapse several absence meanings. If the distinction matters, add a status or reason rather than guessing later. For example, a tax identifier might be missing because it is not applicable, awaiting verification, refused, or unknown.
A design can use a nullable value plus a constrained reason code, with a CHECK that keeps the pair consistent. The goal is truthful representation. NOT NULL should not be used to hide uncertainty, and NULL should not be used when the business needs a more precise state.
Adding NOT NULL to a New Table
For a new table, add NOT NULL directly to every unconditionally required column. Also define defaults, checks, keys, and relationships so the full rule is visible in one schema definition. Test successful and rejected inserts before application release.
Review generated identity and computed-column behavior for the chosen DBMS. A value can be required without callers supplying it manually when the database reliably generates it. What matters is that every stored row satisfies the contract.
Adding NOT NULL to Existing Data
Changing a populated nullable column is a data migration, not merely a DDL edit. Existing NULL values violate the proposed rule, and active applications may still create new ones. First profile the data, investigate why NULL exists, and confirm that the requirement applies to historical as well as future rows.
SELECT COUNT(*) AS null_count
FROM customers
WHERE email IS NULL;
Do not blindly backfill a convenient value. Determine the legitimate source, transform the workflow, or preserve nullability when historical uncertainty is real.
Safe Expand-and-Constrain Migration
A production-safe sequence separates application rollout, data repair, and enforcement. Add a new column as nullable when necessary, update writers to supply it, backfill old rows in controlled batches, verify no violations, then add NOT NULL. Update readers before removing transitional behavior.
- Confirm the business rule and NULL semantics.
- Measure and classify existing NULL values.
- Stop or detect new invalid writes.
- Deploy compatible application and ETL changes.
- Backfill with a legitimate value or source.
- Validate zero violations under ongoing traffic.
- Enforce NOT NULL and monitor failures.
Cross-Database ALTER Syntax
Syntax differs, and some platforms require restating the data type. Always verify the current vendor documentation and test on the deployed version.
-- PostgreSQL
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
-- SQL Server
ALTER TABLE customers
ALTER COLUMN email VARCHAR(254) NOT NULL;
-- MySQL
ALTER TABLE customers
MODIFY COLUMN email VARCHAR(254) NOT NULL;
-- Oracle
ALTER TABLE customers
MODIFY (email NOT NULL);
SQLite has more limited direct alteration capabilities and may require creating a replacement table, copying data, and renaming it. Migration tools can abstract some syntax, but not the business and operational decisions.
Large-Table Locking and Validation
On a large table, adding NOT NULL can scan data, acquire strong locks, wait behind long transactions, grow replication lag, or block writes. Exact behavior depends on the DBMS, version, table state, and migration path. A fast development test is not evidence of safe production execution.
Measure on production-like volume, monitor lock queues, choose timeouts and deployment windows, and prepare rollback or fix-forward steps. Some databases support staged validation patterns that reduce blocking, but those techniques are product-specific and still require testing.
Removing NOT NULL Is Also a Contract Change
Relaxing a constraint may be syntactically easy, but downstream code may assume the value always exists. Queries, APIs, serializers, analytics, caches, and language types may not handle a new NULL state.
Define what NULL will mean, update consumers first, test joins and aggregates, and then relax the schema. Tightening affects existing data and writers; relaxing affects readers and semantics. Both directions require migration planning.
Bulk Imports, Staging, and Core Tables
Raw staging tables may allow NULL so an import can capture malformed or incomplete source rows and report all validation errors. The validated core table should enforce the business requirement with NOT NULL and other constraints.
This difference is intentional: staging represents received data, while core tables represent accepted business data. Do not weaken the core schema because one source is unreliable. Quarantine failures with batch IDs, reasons, and lineage, then load only records that satisfy the target contract.
ORM and Language Nullability
Application models should align with the database. A non-nullable language property mapped to a nullable column may fail when legacy data is read. A nullable property mapped to NOT NULL may compile but permit an invalid object until database write time.
Generate and review migrations rather than assuming ORM conventions are correct. Integration tests should inspect actual schema metadata and exercise NULL failures. Database constraints remain authoritative for stored integrity, while application types improve earlier feedback.
API Required Fields and Database NOT NULL
An API field marked required means the field is required for that operation, not necessarily that the underlying database column must always be non-NULL. A draft-creation endpoint may allow partial data that becomes mandatory only at submission. Different API versions may also have transitional contracts.
Separate transport validation, workflow validation, and structural database invariants. Use NOT NULL for unconditional persisted requirements. Use state-dependent checks or transaction logic for conditional requirements, and document mappings between API fields and storage.
Historical Data Can Have Different Completeness
A requirement introduced today may not be knowable for records created years ago. Backfilling an invented value only to satisfy NOT NULL damages history. Options include retaining nullability, storing a reason, limiting the guarantee to new records, deriving from a trusted source, or migrating historical rows into an explicit unknown domain state.
The choice must be visible to reports and consumers. A schema constraint describes all rows in the table, so do not impose it until the data and semantics truly support it.
Data Integrity and Data Quality Are Different
NOT NULL guarantees presence, not truth. A required email can still be malformed, unreachable, duplicated under a different case rule, or assigned to the wrong person. A timestamp can exist but be in the future. Constraints establish minimum structural integrity; quality processes assess accuracy, freshness, completeness, and fitness for use.
Monitor NULL rates on optional but important columns, constraint failures, backfill progress, and source-specific defects. Optional does not mean irrelevant. Data-quality thresholds can reveal operational problems without turning a legitimately nullable field into a false mandatory rule.
Analytics and Unknown Members
Analytical warehouses sometimes use explicit "Unknown" dimension members so facts with missing source keys still participate in grouped reports. This is a controlled modeling technique with a documented surrogate key, not the same as replacing every operational NULL with arbitrary text or zero.
Operational, staging, and analytical schemas can have different integrity roles. Preserve lineage so consumers know whether a value was unknown at source, failed validation, or became unavailable during processing.
Audit and Soft-Delete Examples
created_at is usually required. created_by may be nullable when legacy or system-created records exist, or it may reference a defined system actor so every row has an accountable source. Choose one policy and apply it consistently.
A soft-delete timestamp is naturally nullable: NULL means active, while a value records deletion time. Adding NOT NULL would destroy that representation. This example shows that NULL can be intentional and powerful when its meaning is precise.
NOT NULL Protects Updates Continuously
The constraint applies after insertion as well as during initial creation. An UPDATE that attempts to clear a required value is rejected even if the row was previously valid. This matters when several services update different subsets of one record or when a generic patch operation accidentally maps an omitted field to NULL.
-- Rejected when email is NOT NULL
UPDATE customers
SET email = NULL
WHERE customer_id = 101;
Use explicit update mappings and distinguish "field omitted" from "set field to NULL" in APIs. Constraint errors can reveal unsafe partial-update logic before it corrupts stored data.
Composite Keys Are Inherently Non-Null
Every column participating in a primary key is non-null because the combined key must identify each row. A junction table can therefore use PRIMARY KEY (order_id, product_id) without repeating NOT NULL in many database products, although explicit declarations may still improve readability or portability.
Composite UNIQUE constraints are different. Their treatment of NULL varies by database, and nullable components can allow combinations that surprise developers. If every part of a business key is mandatory, declare each part NOT NULL and test duplicate behavior on the actual DBMS.
Generated and Computed Values
Identity columns, sequences, generated columns, and computed expressions can satisfy a required value without the application sending it. Their generation rule must still produce valid results for every stored row. A computed expression may become NULL when one input is nullable, depending on its definition and database metadata.
Review how the DBMS reports nullability for generated columns and views. If a generated value is used as a key or business invariant, test inserts covering NULL inputs and conversion failures. Database generation reduces client responsibility but does not remove the need for clear semantics.
Nullability Is Part of Schema Documentation
Tools, ORMs, code generators, data catalogs, and database clients inspect nullability metadata. A correct NOT NULL declaration communicates the contract automatically and can generate safer language types, forms, and validation. A rule that exists only in a wiki cannot provide the same machine-readable guarantee.
Still document why a value is required, especially when the reason is not obvious. Include lifecycle, source, default, sensitive classification, and migration history in the data dictionary. Good metadata explains both constrained and nullable columns so future teams do not "fix" intentional NULL states.
Monitor the Rollout After Enforcement
A successful ALTER statement does not prove every production writer is compatible. Watch constraint-violation counts, application error rates, failed queue messages, import rejects, transaction latency, locks, and replication health after deployment. Break metrics down by service, endpoint, batch, and source system where possible.
Unexpected violations may identify an old application version, undocumented integration, race condition, or a misunderstood business case. Do not immediately remove the constraint to silence errors. Determine whether the writer is wrong or whether the schema rule itself is incomplete, then apply a controlled fix with preserved evidence.
Common NOT NULL Myths
- NOT NULL does not prevent empty or whitespace-only strings.
- NOT NULL does not make a value unique or valid for its domain.
- DEFAULT does not automatically prohibit explicit NULL.
- UNIQUE and FOREIGN KEY do not automatically make a column mandatory.
- CHECK may accept NULL when its condition evaluates to UNKNOWN.
- A NOT NULL base column can appear as NULL in an outer-join result.
- Adding NOT NULL to a large table is not always instant or nonblocking.
- Removing NOT NULL is not safe until consumers understand the new state.
- Unknown should not always be NULL when the reason must be distinguished.
- Every column should not be NOT NULL merely to appear stricter.
Production Safety Checklist
- Confirm that the requirement is unconditional for every valid row.
- Document what NULL would mean and why it is prohibited.
- Combine NOT NULL with keys, checks, defaults, and foreign keys as needed.
- Validate blank strings and domain values separately.
- Count and classify existing NULLs before changing a populated table.
- Update applications, APIs, ETL, imports, and administrative writers.
- Use legitimate backfill values rather than fake placeholders.
- Test DBMS syntax, scans, locks, replication, and migration duration.
- Prepare rollback or fix-forward procedures and monitor constraint errors.
- Review language, ORM, API, and analytical consumer nullability.
- Preserve NULL when it accurately represents workflow or history.
- Test accepted and rejected inserts and updates after deployment.
Interview Questions and Answers
What does NOT NULL do? It prevents SQL NULL from being stored in a column and expresses that a value is always required.
Is NULL equal to zero or an empty string? No. They represent different states, with an Oracle empty-string nuance for character data.
Does NOT NULL prevent blank text? No. Add suitable text validation when blank strings are invalid.
Does DEFAULT imply NOT NULL? No. DEFAULT supplies a value in applicable omissions; NOT NULL prohibits NULL.
Does a foreign key imply NOT NULL? No. A nullable foreign key permits an absent relationship while validating any supplied value.
Why combine CHECK with NOT NULL? NOT NULL guarantees presence, while CHECK restricts the value's domain or cross-column consistency.
How do you add NOT NULL safely? Update writers, backfill legitimate values, validate all rows, assess locks, apply the constraint, and monitor.
Can query output be NULL from a NOT NULL column? Yes. Outer joins, aggregates, and expressions can produce NULL results.
When should a column remain nullable? When absence, unknown state, non-applicability, or a not-yet-occurred event is legitimate.
Key Takeaway
NOT NULL is a precise statement that every stored row requires a value for one attribute. It is valuable because it protects the table across all writers and documents a core invariant. It is also intentionally limited: it does not guarantee uniqueness, range, format, truth, or business completeness by itself.
EFFECTIVE NOT NULL DESIGN = TRUE MANDATORY ATTRIBUTE
+ CLEAR NULL SEMANTICS
+ CORRECT DEFAULTS
+ COMPLEMENTARY CONSTRAINTS
+ APPLICATION ALIGNMENT
+ SAFE DATA MIGRATION
+ PRODUCTION VALIDATION
Use NOT NULL when absence would make every row invalid. Preserve nullability when NULL honestly represents workflow, uncertainty, or history. For existing tables, treat the change as a coordinated data and application migration, not just a one-line ALTER statement.