SQL Not Null Constraint With Example

Constraints Definition:

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Constraints provide a standard mechanism to maintain the accuracy and integrity of the data inside a database table.

1. NOT NULL Constraints

A Not Null constraint ensures that the column in the table cannot have Null values, however it may have duplicate values.

This means if NOT NULL constraint is applied on a column then you cannot insert a new row in the table without adding a non-NULL value for that column.

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


Syntax:

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

Example 1:


 SQL> CREATE TABLE std_info(
    no NUMBER(3,0) PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    address VARCHAR(70)
 );
-----------------------------
  Table created.
-----------------------------

Now, let's Describe(view) the table std_info we have just created

  
 SQL> DESCRIBE std_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 2:


SQL> ALTER TABLE std_info MODIFY address VARCHAR2(70) NOT NULL;
--------------
Table altered.

Now, let's Describe(view) the table std_info we have just created


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

Read Also: