The NOT NULL constraint in SQL enforces that a column cannot contain NULL values, ensuring data integrity and completeness in database tables.

This constraint can be applied during table creation or through table modifications to guarantee that specific columns always contain valid data.

Defining NOT NULL During Table Creation

When creating a new table, you can specify NOT NULL directly in the column definition:

CREATE TABLE users (
    user_id INT NOT NULL,
    username VARCHAR(40) NOT NULL,
    full_name VARCHAR(60) NOT NULL,
    phone_number VARCHAR(15)
);

In this example, user_id, username, and full_name columns must contain values, while phone_number can remain empty.

Adding NOT NULL to Existing Tables

For existing tables, you can add NOT NULL constraints using ALTER TABLE:

ALTER TABLE users
MODIFY phone_number VARCHAR(15) NOT NULL;

This statement converts the phone_number column from nullable to requiring values.

Combining NOT NULL with Default Values

You can pair NOT NULL with DEFAULT to provide automatic values when none are specified:

CREATE TABLE transactions (
    transaction_id INT NOT NULL,
    user_id INT NOT NULL,
    transaction_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

If no transaction_time is provided during insertion, the current timestamp will be used automatically.

Inserting Data with NOT NULL Constraints

When enserting records into tables with NOT NULL columns, you must provide values for those columns:

INSERT INTO transactions (transaction_id, user_id)
VALUES (100, NULL);

INSERT INTO transactions (transaction_id, user_id)
VALUES (100, 25);

The first insert will fail because user_id cannot be NULL, while the second succeeds with valid data.

Updating NOT NULL Columns

You can update NOT NULL columns with new values as long as they're not NULL:

UPDATE users
SET username = 'john_doe'
WHERE user_id = 25;

NOT NULL constraints are esssential for mainatining data quality and preventing incomplete records in database systems.