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.

SQL 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

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

 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

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

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)