Home » Uncategorized » SQL NOT NULL Constraint

SQL NOT NULL Constraint

Overview: You will learn and understand, how to use the SQL NOT NULL constraint to ensure that column should not have NULL value.

Understanding of NOT NULL

SQL NOT NULL constraint is the way to make sure that you can not update or insert new record without any a value to the column (meaning there should be values for column). It helps to avoid acceptance of NULL values for a columns. It is also called column level constraints.

Syntax:

CREATE TABLE Table_name(
             Column1 datatype1 NOT NULL,
             Column2 datatype2 NOT NULL,
             Column3 datatype3
             );

Lets take examples how to create NOT NULL constraint

EXAMPLE #1: Create NOT NULL Constraint column

Lets create table using CREATE TABLE command and define NOT NULL constraint for few column

In below code we have created two Columns as NOT NULL constraints Employee_name and email

So, if you will not entre any name or email id for employee the table will through error that value should not be NULL, so that these two columns always should have some value for Employee_info table.

CREATE TABLE Employee_info(
             Employee_id INT PRIMARY KEY,
             Employee_name VARCHAR(255) NOT NULL,
             email VARCHAR(255) NOT NULL,
             phone VARCHAR(10)
             );

EXAMPLE #2: How to add NOT NULL constraint to existing column

If you want to add NOT NULL constraint to an existing column in SQL table the please follow below steps

  • First, You need to update SQL table columns to ensure there is no NULL values in that column using UPDATE command as shown below syntax
UPDATE Table_name
SET column_name = <value>    // <value> is replace any text like  "not available Data"
WHERE column_name IS NULL;
  • Second, Now add SQL NOT NULL constraint by using ALTER TABLE command with below syntax
ALTER TABLE Table_name
ALTER COLUMN column_name data_type NOT NULL;

Example: Add NOT NULL constraint to existing column

Lets take example for Employee_info table and add NOT NULL constraint for phone column

First update Phone column so that it should not be NULL value

UPDATE Employee_info
SET Phone = "Phone number Not available"    
WHERE Phone IS NULL;

Second apply ATLER TABLE command to cadd NOT NULL constraint

ALTER TABLE Employee_info
ALTER COLUMN Phone VARCHAR(10) NOT NULL;

Change NOT NULL constraint

To drop NOT NULL constraint for existing table column, you need to use the ALTER TABLE command as shown below syntax code:

ALTER TABLE Table_name
ALTER COLUMN column_name data_type NULL;


Lets take example for Phone column from Employee_info table to remove the NOT NULL constraint

ALTER TABLE Employee_info
ALTER COLUMN Phone VARCHAR(20) NULL;