SQL NOT NULL Constraint

Introduction to NOT NULL

The NOT NULL constraint in SQL is used to prevent a column from having a NULL value. When a column is defined with the NOT NULL constraint, it must contain a value, and inserting or updating a record with a NULL in that column will result in an error.

Why Use NOT NULL?

Using the NOT NULL constraint is essential when you want to ensure that a column always has a value. This is particularly useful for columns that are critical to the integrity of your data, such as primary keys, foreign keys, or any other field where the absence of a value would be problematic.

Syntax

The NOT NULL constraint is defined when creating or altering a table. Here’s the basic syntax:

CREATE TABLE table_name (
    column_name datatype NOT NULL,
    ...
);

Example

Let’s consider an example where we create a table called users with a NOT NULL constraint on the email column. This ensures that every user must have an email address.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) NOT NULL
);

Now, if you try to insert a record into the users table without providing an email, SQL will throw an error:

INSERT INTO users (id, name) VALUES (1, 'John Doe');
-- This will result in an error because the email column cannot be NULL.

Altering a Table to Add NOT NULL

If you have an existing table and want to add a NOT NULL constraint to a column, you can use the ALTER TABLE statement:

ALTER TABLE users
MODIFY email VARCHAR(255) NOT NULL;

Removing NOT NULL Constraint

If you need to remove the NOT NULL constraint from a column, you can modify the column as follows:

ALTER TABLE users
MODIFY email VARCHAR(255) NULL;

Conclusion

The NOT NULL constraint is a simple yet powerful tool in SQL to ensure data integrity. By using NOT NULL, you can enforce the presence of crucial data in your database columns, preventing errors and maintaining consistency.