SQL Not Null

The NOT NULL constraint ensures that a column cannot have a NULL value. This means that every time you insert a new record into the table, you must provide a value for columns that have this constraint — leaving them empty (NULL) will result in an error.

SQL Not Null Syntax

CREATE TABLE table_name (
  column_name datatype NOT NULL
);

Or, if the table already exists:

ALTER TABLE table_name
ADD column_name datatype NOT NULL DEFAULT default_value;

Note: In SQLite, if you add a NOT NULL column using ALTER TABLE, you must also provide a DEFAULT value — otherwise, it won’t work.

SQL Not Null Example

This query creates a simple Drivers table where first_name and last_name must always have values:

CREATE TABLE Drivers (
  driver_id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL
);

Now if you try to insert a patient without a first or last name

INSERT INTO Drivers (driver_id, first_name) VALUES (1, 'Max');

You’ll get an error, because last_name is required and cannot be NULL.

SQL Not Null on Create Table

This query creates a Formula1 table with first_name and team set to NOT NULL:

CREATE TABLE Formula1 (
  driver_id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT,
  team TEXT NOT NULL
);

Every time a new driver is added, both first_name and team must be provided.

Example insert that works:

INSERT INTO Formula1 (driver_id, first_name, last_name, team)
VALUES (1, 'Max', 'Verstappen', 'RedBull');

Example insert that fails:

INSERT INTO Formula1 (driver_id, last_name, team)
VALUES (2, 'Hamilton', 'Ferrari');

Fails because first_name is missing.

SQL Not Null on Alter Table

If you already have a table and you want to add a new column that cannot be NULL, you must use ALTER TABLE and give a DEFAULT value.

For example, adding an email column to Patients:

ALTER TABLE Patients
ADD email TEXT NOT NULL DEFAULT 'noemail@domain.com';

This works in SQLite because it gives existing rows a non-NULL value immediately.

If you try to do this without a DEFAULT:

-- This will FAIL in SQLite:
ALTER TABLE Patients
ADD email TEXT NOT NULL;

You’ll get an error because existing records would have NULL in the new column, which isn’t allowed by the NOT NULL constraint.

SQL Not Null Labs