SQL NOT NULL Constraint

SQL NOT NULL constraint is used to avoid NULL values to be entered into a column. It is another way of say that the value for this column is mandatory.  SQL NOT NULL constraint can appear only as a single column constraint.  A column with NOT NULL constraint can have duplicate values but none of the values should be NULL. As it seems, SQL NOT NULL constraint is exact opposite of UNIQUE constraint. Both UNIQUE and NOT NULL constraints can be combined together to create primary key effect.

ADDING A NOT NULL CONSTRAINT
SQL NOT NULL can be added using

1. Create Statement
2. Alter Statement

 

Adding  NOT NULL constraint using CREATE:

Example:
The following query will define SQL NOT NULL on column dept_name of department table using CREATE statement:

CREATE TABLE department (dept_no NUMBER(2), dept_name VARCHAR2(15) NOT NULL, mgr_id NUMBER,  mgr_start_date DATE);

Example 2:
The following query is same to one above except that it defines SQL NOT NULL constraint with name nn_dept_name

CREATE TABLE department (dept_no NUMBER(2), dept_name VARCHAR2(15) CONSTRAINT nn_dept_name NOT NULL, mgr_id NUMBER,  mgr_start_date DATE);

 

Adding NOT NULL using ALTER:

Example:
The following query will define SQL NOT NULL on dept_name column of department given that there is no NOT NULL existed earlier for this column:

ALTER TABLE department MODIY (dept_name NOT NULL);

Example 2:
The following query is same as one above except that it defines SQL NOT NULL constraint with name nn_dept_name

ALTER TABLE department MODIFY (dept_name CONSTRAINT nn_dept_name NOT NULL);

 

DROPPING A NOT NULL

Example:
The following statement will drop SQL NOT NULL from dept_name of department table:

ALTER TABLE department MODIFY (dept_name NULL);

Example 2:
The following statement will drop SQL NOT NULL identified by its name:

ALTER TABLE department DROP CONSTRAINT nn_dept_name;

SQL NOT NULL ensures value to be entered in a table by avoiding NULL values. This means columns in a SQL NOT NULL can not contain null values. Each table may have more than one NOT NULL.