Summary -

In this topic, we described about the below sections -

Why NOT NULL Constraint?

While Inserting data in table we do not provide any values for optional columns, By Default those columns store NULL values. If we don't need NULL values on those columns, we can use NOT NULL Constraint. NOT NULL Constraint does not allow NULL values on by defining column as NOT NULL.

What is NOT NULL Constraint?

By defining a column with NOT NULL constraint it does not allow null values. We can create NOT NULL Constraint while CREATE TABLE Statement on creating table or ALTER TABLE statement to add NOT NULL Constraint on existing table. We can add NOT NULL constraint on multiple columns in one table. NOT NULL constraint allows duplicate values. If we defined a column as NOT NULL while inserting or updating values on table, we must and should give value to that specified column.

Points to know on Not Null Constraint -

  • Not Null Constraint allow duplicate values.
  • Not Null Constraint does not allow null values.
  • User can create multiple columns with Not Null Constraint.
  • User can add another constraint like UNIQUE, PRIMARY KEY to Not Null Constraint.
  • By using Not Null Constraint with PRIMARY KEY user can identify rows uniquely.

Syntax for creating NOT NULL constraint while creating table – 

CREATE TABLE table_name (
   Column1 datatype [NOT NULL],
   Column2 datatype [NOT NULL],
   Column3 datatype [NOT NULL], 
    …,    
    …,        
   ColumnN datatype [NOT NULL]);

Syntax for creating NOT NULL constraint on existing table -

ALTER TABLE table_name 
 ALTER COLUMN column_name datatype [NOT NULL];

Syntax for deleting NOT NULL constraint on existing table – 

ALTER TABLE table_name
 ALTER COLUMN column_name datatype;

Examples

Scenario – Creating NOT NULL constraint on table while creating table.

Requirement – Creating emp_id column as NOT NULL on employee_details table while creating table. The query was as follows - 

CREATE TABLE employee_details ( 
     emp_id   INT NOT NULL,
     emp_name VARCHAR (20), 
     designation VARCHAR (10));

By executing above query, we can create NOT NULL constraint on employee_details table.


Scenario – Updating NOT NULL constraint to a column on existing table.

Requirement – updating dept_id column as NOT NULL on existing employee_details table. 

ALTER TABLE employee_details
ALTER COLUMN dept_id INT NOT NULL;

By executing above query, we can update dept_id as NOT NULL on existing employee_details table.


Note - Before executing above query, we should know the column that is going to add NOT NULL should not have NULL values.

Scenario – Delete existing NOT NULL constraint.

Requirement – Delete NOT NULL constraint on existing column emp_id on employee_details table. The query was as follows - 

ALTER TABLE employee_details ALTER COLUMN emp_id INT;

By executing above query, we can delete existing NOT NULL constraint on employee_details table.

When do not use NOT NULL constraint

When table had an optional data column, we do not use NOT NULL constraint on the specific table. Let us assume an employee_details table with columns employee_first_name, employee_middle_name, employee_last_name, employee_address. Among those columns, employee_middle_name is optional. In these types of cases, user need not use NOT NULL Constraint on employee_details table.