sql_notnull

SQL NOT NULL Constraint

By default, a column in a database table can hold NULL values. A NULL value signifies an absence of data, which is different from an empty string or zero.

The SQL NOT NULL Constraint

The NOT NULL constraint enforces that a column cannot accept NULL values. This means that every row in the table must have a value for that column.

When a NOT NULL constraint is applied to a column, you cannot insert a new record or update an existing record if it would result in a NULL value in that column. This ensures data integrity by requiring certain fields to always contain a value.

Defining NOT NULL during Table Creation

The following SQL statement creates a Persons table where the ID and LastName columns are enforced to not accept NULL values:

CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(255)
);

In this example, FirstName, Address, and City can still accept NULL values.

Adding NOT NULL to an Existing Column

If a table already exists, you can add a NOT NULL constraint to a column using the ALTER TABLE statement. The exact syntax can vary slightly between database systems.

SQL Server / Oracle / MySQL (using MODIFY) / PostgreSQL:

ALTER TABLE Persons
MODIFY COLUMN FirstName VARCHAR(255) NOT NULL; -- MySQL syntax

-- SQL Server syntax:
-- ALTER TABLE Persons
-- ALTER COLUMN FirstName VARCHAR(255) NOT NULL;

-- Oracle syntax:
-- ALTER TABLE Persons
-- MODIFY FirstName VARCHAR(255) NOT NULL;

-- PostgreSQL syntax:
-- ALTER TABLE Persons
-- ALTER COLUMN FirstName SET NOT NULL;

Important: If the column already contains NULL values, attempting to add a NOT NULL constraint will result in an error. You must first update any NULL values in that column to a non-NULL value or remove the rows containing NULLs before applying the constraint.

Removing a NOT NULL Constraint

To remove a NOT NULL constraint, you also use ALTER TABLE, allowing the column to accept NULL values.

SQL Server / Oracle / MySQL (using MODIFY) / PostgreSQL:

ALTER TABLE Persons
MODIFY COLUMN FirstName VARCHAR(255) NULL; -- MySQL syntax (NULL is often default)

-- SQL Server syntax:
-- ALTER TABLE Persons
-- ALTER COLUMN FirstName VARCHAR(255) NULL;

-- Oracle syntax:
-- ALTER TABLE Persons
-- MODIFY FirstName VARCHAR(255) NULL;

-- PostgreSQL syntax:
-- ALTER TABLE Persons
-- ALTER COLUMN FirstName DROP NOT NULL;

Why Use NOT NULL?

Mandatory Data: Ensures essential information is always present (e.g., a user must have an email, an order must have an order date).

Data Integrity: Prevents missing data that could lead to errors or incorrect analysis.

Simplified Queries: Avoids the need to handle NULLs explicitly in queries (e.g., WHERE ColumnName IS NOT NULL) if the data is guaranteed to exist.

However, use NOT NULL judiciously. If a piece of information is genuinely optional, the column should allow NULLs.