# SQL NOT NULL Constraint: Require a Value in a Column

- URL: https://betteranalyst.com/sql/not-null

SQL guide · Constraints

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

- [SQL DEFAULT](https://betteranalyst.com/sql/default)
- [SQL CHECK](https://betteranalyst.com/sql/check)
- [SQL PRIMARY KEY](https://betteranalyst.com/sql/primary-key)
- [SQL IS NULL](https://betteranalyst.com/sql/is-null)
- [SQL ALTER TABLE](https://betteranalyst.com/sql/alter-table)
- [SQL CREATE TABLE](https://betteranalyst.com/sql/create-table)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)