Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


SQL NOT NULL Constraint


SQL NOT NULL constraint enforces to a column is always contain a value. This means thats you can not insert NULL (blank) value in this field.

NOT NULL constraint applied only at column level. You should manually define NOT NULL constraint because table column set NULL value.

SQL NOT NULL Constraint Rules

  • A NULL values is different from a blank or zero.
  • A NULL value can be inserted into the columns of any Datatype.
Datatype Action Values
NUMBER Error Produce blank, space, special character, NULL
NUMBER Allow Only O or 1 to 9
VARCHAR2 Error Produce blank, space, special character, NULL
VARCHAR2 blank NULL, space, special character

SQL Not Null Constraint


NOT NULL Defined only at Column level

Define NOT NULL constraint at column level with other column attributes,

Syntax :

CREATE TABLE table_name(
	column_name datatype[(size)] NOT NULL,
	column_name datatype[(size)] NOT NULL,
	....
);

Example Statement :

 SQL> CREATE TABLE emp_info(
 	no NUMBER(3,0) PRIMARY KEY,
 	name VARCHAR(30) NOT NULL,
	address VARCHAR(70)
);

Table created.

 SQL> DESCRIBE emp_info;
 Name                         Null?     Type
 ---------------------------- --------- ----------------------------
 NO                           NOT NULL  NUMBER(3)
 NAME                         NOT NULL  VARCHAR2(30)
 ADDRESS                                VARCHAR2(70)


ADD NOT NULL constraint (ALTER TABLE)

ALTER TABLE statement to add NOT NULL constraint in existing table column.

Syntax :

ALTER TABLE table_name
	MODIFY column_name datatype[(size)] NOT NULL;

Example Statement :

SQL> ALTER TABLE emp_info MODIFY address VARCHAR2(70) NOT NULL;

Table altered.

SQL> DESCRIBE emp_info;
 Name                         Null?     Type
 ---------------------------- --------- ----------------------------
 NO                           NOT NULL  NUMBER(3)
 NAME                         NOT NULL  VARCHAR2(30)
 ADDRESS                      NOT NULL  VARCHAR2(70)

DROP NOT NULL constraint (ALTER TABLE)

ALTER TABLE statement to drop NOT NULL constraint in existing table column.

Syntax :

ALTER TABLE table_name
	MODIFY column_name datatype[(size)] NULL;

Example Statement :

SQL> ALTER TABLE emp_info MODIFY address VARCHAR2(70) NULL;

Table altered.

SQL> DESCRIBE emp_info;
 Name                         Null?     Type
 ---------------------------- --------- ----------------------------
 NO                           NOT NULL  NUMBER(3)
 NAME                         NOT NULL  VARCHAR2(30)
 ADDRESS                      		    VARCHAR2(70)