SQL Not Null

Why NOT NULL matters

By default, most columns accept NULL, which is the database's way of saying a value is missing or unknown. Sometimes that is fine, but for fields that must always be present, such as a customer's name or an order's total, allowing NULL creates gaps that break reports and confuse applications. The NOT NULL constraint closes that gap by rejecting any insert or update that would leave the column empty.

Defining NOT NULL at table creation

CREATE TABLE Customers (
    CustomerID   INT NOT NULL,
    FirstName    VARCHAR(50) NOT NULL,
    LastName     VARCHAR(50) NOT NULL,
    PhoneNumber  VARCHAR(20)
);

Here CustomerID, FirstName, and LastName must all be supplied on every insert. PhoneNumber has no NOT NULL, so it may be left empty when a phone number is unknown.

-- Fails: LastName is required but missing
INSERT INTO Customers (CustomerID, FirstName)
VALUES (1, 'Maria');

-- Succeeds: every NOT NULL column has a value
INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (2, 'Devon', 'Clarke');

Adding or removing NOT NULL later

You can change whether a column allows NULL after the table exists, but the exact syntax varies between database systems. In SQL Server you use ALTER COLUMN, while MySQL uses MODIFY. Note that a column can only be switched to NOT NULL if it currently contains no NULL values.

-- SQL Server
ALTER TABLE Customers
ALTER COLUMN PhoneNumber VARCHAR(20) NOT NULL;

-- MySQL
ALTER TABLE Customers
MODIFY PhoneNumber VARCHAR(20) NOT NULL;