SQL guide · Constraints

SQL NOT NULL Constraint: Require a Value in a Column

By default, a column in SQL accepts NULL, which means the value is unknown or missing. A NOT NULL constraint changes that: every row must supply a real value for the column, and inserts or updates that leave it empty are rejected. It is the simplest and most common constraint, and one of the most effective ways to keep data trustworthy.

Use NOT NULL for any field where a missing value would make the row meaningless or break your queries, such as an order date, a customer name or a foreign key that must always point to something. Nulls have awkward behaviour: comparisons with them return unknown rather than true or false, aggregates skip them, and col <> 'x' does not match rows where col is null. Preventing nulls up front removes a whole class of subtle reporting mistakes.

NOT NULL is often combined with a DEFAULT value, so that inserts which omit the column still succeed. It is also implied by PRIMARY KEY. Remember that an empty string is not the same as null, so NOT NULL will not stop someone from storing ''; pair it with a CHECK constraint if you also need to block blank text.

Basic syntax

CREATE TABLE table_name (
  column_name data_type NOT NULL,
  other_column data_type NOT NULL DEFAULT value
);

-- Add or remove on an existing column (varies by database)
ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;      -- PostgreSQL, Oracle (MODIFY)
ALTER TABLE table_name
ALTER COLUMN column_name data_type NOT NULL; -- SQL Server

NOT NULL is a column constraint, so it goes after the data type in the column definition. Changing an existing column uses a different form in each database, shown in the dialect notes. All existing rows must already have a value before the change is accepted.

SQL NOT NULL examples

Required columns on CREATE TABLE

The name and order date must always be present, while phone is optional and can stay null. Inserting a customer without a name fails immediately, rather than leaving a blank record in the table.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  full_name   VARCHAR(100) NOT NULL,
  email       VARCHAR(255) NOT NULL,
  phone       VARCHAR(30)
);

-- Fails: full_name is required
INSERT INTO customers (customer_id, email)
VALUES (1, 'ada@example.com');

NOT NULL with a DEFAULT

Giving the column a default lets inserts leave it out. Here every new order starts with the status pending, and the column can never be emptied afterwards.

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  status     VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders (order_id) VALUES (5001);

Make an existing column NOT NULL

Before enforcing the rule, fill in the missing values, otherwise the statement fails. The update below gives blank emails a placeholder, then PostgreSQL's syntax is used to lock the column down.

UPDATE customers
SET email = 'unknown@example.com'
WHERE email IS NULL;

-- PostgreSQL
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

Allow nulls again

If the rule turns out to be too strict, you can remove it. In PostgreSQL this is DROP NOT NULL; other databases redefine the column without the constraint.

-- PostgreSQL
ALTER TABLE customers
ALTER COLUMN phone DROP NOT NULL;

-- SQL Server
ALTER TABLE customers
ALTER COLUMN phone VARCHAR(30) NULL;

Where SQL dialects differ

PostgreSQL: Use ALTER TABLE t ALTER COLUMN c SET NOT NULL and DROP NOT NULL. Setting the constraint scans the table to confirm no nulls exist; from PostgreSQL 12, a validated CHECK (c IS NOT NULL) constraint lets it skip that scan.

MySQL: There is no SET NOT NULL form. You restate the full column definition with ALTER TABLE t MODIFY c VARCHAR(100) NOT NULL (or CHANGE). Forgetting existing attributes such as DEFAULT in the redefinition removes them.

SQL Server: Use ALTER TABLE t ALTER COLUMN c data_type NOT NULL, restating the type. Columns are nullable by default unless the session setting ANSI_NULL_DFLT_ON is changed, so state NULL or NOT NULL explicitly.

SQLite: You cannot change nullability with ALTER TABLE. Create a new table with the constraint, copy the data across and rename it. ALTER TABLE ADD COLUMN with NOT NULL is allowed only if you also supply a non-null default.

Oracle: Use ALTER TABLE t MODIFY (c NOT NULL) and MODIFY (c NULL). Oracle treats an empty string as NULL, so a NOT NULL column rejects '' as well, unlike most other databases.

Common mistakes

Adding NOT NULL to a column that still contains nulls. Clean the data first with an UPDATE, or the ALTER TABLE is rejected.
Believing an empty string satisfies the opposite of null. In most databases '' is a valid, non-null value, so add a CHECK constraint to block blanks.
Adding a new NOT NULL column without a default to a populated table. The existing rows have no value to hold, so supply a DEFAULT or add the column as nullable first.
Using = NULL or <> NULL in queries. Always test with IS NULL or IS NOT NULL.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator