SQL - NOT NULL

SQL - The NOT NULL Constraint

The NOT NULL constraint is a fundamental rule for ensuring data integrity in a database. It mandates that a column cannot hold a NULL value. By default, columns in a table can store NULL values, but applying this constraint forces a value to be present for that column in every row, preventing missing or undefined data.

Defining NOT NULL on Table Creation

The most common way to apply a NOT NULL constraint is during table creation using the CREATE TABLE statement. You specify the NOT NULL keywords immediately after the column’s data type.

Syntax

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype, -- This column can be NULL
    column3 datatype NOT NULL,
    ...
);

Example

Let’s create a PRODUCTS table where the product’s ID, NAME, and PRICE are essential and must not be null.

CREATE TABLE PRODUCTS (
    ID INT PRIMARY KEY, -- Primary keys are implicitly NOT NULL
    NAME VARCHAR(100) NOT NULL,
    PRICE DECIMAL(10, 2) NOT NULL,
    SKU VARCHAR(50),
    DESCRIPTION TEXT
);

Now, if we attempt to insert a row without a NAME or with a NULL value for PRICE, the database will reject the operation and return an error.

-- This statement will FAIL
INSERT INTO PRODUCTS (ID, NAME, PRICE) VALUES (101, NULL, 49.99);

-- ERROR: [database-specific error message, e.g., 'Column 'NAME' cannot be null']

A successful insert must provide values for all NOT NULL columns:

-- This statement will SUCCEED
INSERT INTO PRODUCTS (ID, NAME, PRICE, SKU) VALUES (102, 'Wireless Mouse', 24.99, 'WM-102');

Adding NOT NULL to an Existing Table

You can add a NOT NULL constraint to a column in an existing table using the ALTER TABLE statement. However, this operation will fail if the column currently contains any NULL values. You must first update all NULL entries to a valid value.

Two-Step Process

Let’s add a NOT NULL constraint to the SKU column of our PRODUCTS table.

Step 1: Update existing NULL values. We need to decide on a sensible default or unique value for any existing rows where SKU is NULL.

-- Assume we have rows with NULL SKU. Let's provide a placeholder.
UPDATE PRODUCTS
SET SKU = 'NOT-SET'
WHERE SKU IS NULL;

Step 2: Apply the NOT NULL constraint. The syntax for this varies across different database systems.

Syntax (PostgreSQL / SQL Server)

ALTER TABLE PRODUCTS
ALTER COLUMN SKU SET NOT NULL;

Syntax (MySQL)

ALTER TABLE PRODUCTS
MODIFY COLUMN SKU VARCHAR(50) NOT NULL;

Removing a NOT NULL Constraint

Similarly, you can remove a NOT NULL constraint to allow NULL values in a column. This is also done with the ALTER TABLE statement.

Syntax (PostgreSQL / SQL Server)

ALTER TABLE PRODUCTS
ALTER COLUMN SKU DROP NOT NULL;

Syntax (MySQL)

In MySQL, you redefine the column, specifying NULL instead of NOT NULL.

ALTER TABLE PRODUCTS
MODIFY COLUMN SKU VARCHAR(50) NULL;

NOT NULL vs. DEFAULT

NOT NULL and DEFAULT are often used together. NOT NULL enforces that a value must exist, while DEFAULT provides a fallback value to use during an INSERT if one isn’t explicitly supplied.

Example

Let’s create a table with a status column that must have a value, and defaults to ‘pending’ if not specified.

CREATE TABLE ORDERS (
    ORDER_ID INT PRIMARY KEY,
    ORDER_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    STATUS VARCHAR(20) NOT NULL DEFAULT 'pending'
);

-- This INSERT doesn't specify STATUS, so it will get the default value 'pending'.
INSERT INTO ORDERS (ORDER_ID) VALUES (1001);

Best Practices and Considerations

• Identify Mandatory Data: Apply NOT NULL to all columns that are essential for a record to be meaningful and functional (e.g., user emails, order dates, transaction amounts).
• Primary Keys: All primary key columns are implicitly NOT NULL, so you don’t need to specify it for them.
• Avoid Overuse: For optional data fields (e.g., middle_name, address_line_2), allowing NULL is appropriate and more flexible than using empty strings or magic values.