SQL Tutorial

🔒 SQL NOT NULL Constraint

The NOT NULL constraint is used to ensure that a column cannot have a NULL value. This is a basic and important concept in SQL, as it guarantees that the column will always contain data when you insert or update records.

1️⃣ Adding NOT NULL Constraint to a Column

When creating a table, you can specify that a column should not allow NULL values by using the NOT NULL constraint. Here's how you can do it:

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

In this example, the employee_name column cannot be left empty when a new record is inserted into the employees table.

2️⃣ Inserting Data into a NOT NULL Column

If you try to insert a NULL value into a column that has the NOT NULL constraint, SQL will throw an error. Here's an example:

INSERT INTO employees (employee_id, employee_name, department)
VALUES (1, NULL, 'HR');

This will cause an error because the employee_name column has a NOT NULL constraint, and you are attempting to insert a NULL value.

3️⃣ Alter an Existing Column to Add NOT NULL

If you have an existing column and you want to add the NOT NULL constraint to it, you can use the ALTER TABLE statement. Here's how:

ALTER TABLE employees
MODIFY employee_name VARCHAR(100) NOT NULL;

This will enforce that all future records inserted into the table will require a non-NULL value in the employee_name column.

4️⃣ NOT NULL with Other Constraints

You can combine the NOT NULL constraint with other constraints such as PRIMARY KEY, UNIQUE, and CHECK. For example, a column can be both a primary key and NOT NULL:

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

In this case, both the employee_id and employee_name columns are required to contain data, ensuring that the table has valid and non-empty records.

🔧 Key Considerations

• Data Integrity: The NOT NULL constraint ensures that your data is always complete and never missing.
• Default Value: If you don’t want to manually insert values for every record, consider using a DEFAULT constraint alongside NOT NULL.
• Primary Key: A primary key column automatically has the NOT NULL constraint because a primary key must contain unique and non-null values.