SQL NOT NULL Constraint Tutorial

Example Table

We will create a table named employees to demonstrate the SQL NOT NULL constraint:

        CREATE TABLE employees (
            employee_id INT PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            department VARCHAR(50)
        );
        

Creating the Table

To create the table, use the following SQL command:

        CREATE TABLE employees (
            employee_id INT PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            department VARCHAR(50)
        );
        

Result:

Command	CREATE TABLE employees (employee_id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, department VARCHAR(50));
Result	A table named employees is created with first_name and last_name columns set to NOT NULL.

Inserting Data

To insert data into the table, use the following SQL command:

        INSERT INTO employees (employee_id, first_name, last_name, department) 
        VALUES (1, 'John', 'Doe', 'HR');
        

Result:

Command	INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (1, 'John', 'Doe', 'HR');
Result	Data is inserted into the employees table.

Attempting to Insert NULL Values

If you try to insert a NULL value into a NOT NULL column, you will get an error:

        INSERT INTO employees (employee_id, first_name, last_name, department) 
        VALUES (2, NULL, 'Smith', 'Finance');
        

Result:

Command	INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (2, NULL, 'Smith', 'Finance');
Result	Error: Column 'first_name' cannot be null.

Important Considerations

When using the NOT NULL constraint, consider the following:

• Use NOT NULL on columns that must have a value.
• It helps to ensure data integrity by preventing NULL values in critical columns.
• Be mindful of default values for NOT NULL columns if applicable.