The NOT NULL constraint acts as a rule enforcing that a specific column cannot store undefined or missing values. This restriction is fundamental for maintaining data integrity, ensuring that every record in the database contains essential information for designated fields.

Applying this constraint during table creation prevents the insertion of incomplete records. For instance, consider a schema for an employee directory where identification and department assignments are mandatory, while a secondary contact method remains optional.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    secondary_email VARCHAR(100)
);

In this structure, the full_name and department columns are strictly enforced. Any attempt to insert a row lacking these values will be rejected by the database engine. Conversely, secondary_email remains unrestricted, allowing for empty entries.

For existing tables, the ALTER TABLE command is used to apply this restriction. To convert an optional field into a mandatory one, the column definition is modified.

ALTER TABLE employees
ALTER COLUMN secondary_email SET NOT NULL;

The NOT NULL constraint is frequently paired with the DEFAULT constraint. This combination ensures that if a user omits a value during an insert operation, the system automatically supplies a preset value, satisfying the non-null requirement automatically.

CREATE TABLE system_logs (
    log_id INT PRIMARY KEY,
    event_message VARCHAR(255) NOT NULL,
    event_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

In the system_logs table above, event_time defaults to the current timestamp if unspecified. This setup prevents null entries without requiring manual input for every transaction.

Data Manipulation Language (DML) operations must strictly adhere to these constraints. During data insertion, omitting a value for a constrained column or explicitly providing NULL results in an error.

-- Valid operation
INSERT INTO employees (emp_id, full_name, department)
VALUES (101, 'Alice Smith', 'Engineering');

-- Invalid operation: triggers constraint violation
INSERT INTO employees (emp_id, full_name, department)
VALUES (102, NULL, 'Human Resources');

Similarly, UPDATE operations must respect these rules. Data within a constrained column can be modified, provided the new value is not null.

UPDATE employees
SET department = 'Product Management'
WHERE emp_id = 101;