PostgreSQL NOT NULL Constraint

• NULL is unknown or missing information. The NULL value is different from empty or zero. For example, we can ask for the email address of a person, if we don’t know, we use the NULL value. In case the person does not have any email address, we can mark it as an empty string.
• PostgreSQL provides the not-null constraint to enforce a column must not accept NULL values. It means that whenever you insert or update data, you must specify a value that is different from the NULL value.
• Any one attempt to put NULL values in that column will be rejected. Columns without the NOT NULL constraint allow NULL values.
• A NOT NULL constraint is a column constraint and can not be used as a table constraint.
• A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL). 

PRACTICAL 1.PostgreSQL NOT NULL CONSRAINT CREATION:

postgres=# CREATE TABLE classes (
    class_no integer NOT NULL,
    school_name   text,    
    student_name text NOT NULL
);
CREATE TABLE

postgres=# \d classes
       Table "public.classes"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 class_no     | integer | not null
 school_name  | text    | 
 student_name | text    | not null

PRACTICAL 2.ADDING PostgreSQL NOT NULL CONSRAINT TO EXISTING COLUMN OF TABLE:

postgres=# CREATE TABLE classes3 (
    class_no integer ,
    school_name   text,    
    student_name text );

CREATE TABLE

postgres=# \d classes3
      Table "public.classes3"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 class_no     | integer | 
 school_name  | text    | 
 student_name | text    | 

To add not-null constraints to "class_no" columns of an existing table, we use the following syntax:

postgres=# alter table classes3 alter column class_no set not null;
ALTER TABLE

postgres=# \d  classes3
      Table "public.classes3"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 class_no     | integer | not null
 school_name  | text    | 
 student_name | text    | 

PRACTICAL 3.INSERTION OPERATION ON CLASS3 TABLE OF NOT NULL CONSTRAINTS:

postgres=# insert into classes3 values(1,'joseph high school','nijam');
INSERT 0 1
postgres=# insert into classes3 values(1,'joseph high school','nijam');
INSERT 0 1

postgres=# insert into classes3 values(6,'joseph high school','');     
INSERT 0 1

you can insert the null on classes3 table column of "student_name"  and "school_name" but you can't insert or update the null values in "class_no" column becouse "class_no" having not null constraint. 

postgres=# insert into classes3 values(9,'joseph high school','null');
INSERT 0 1
postgres=# insert into classes3 values(9,'joseph high school','');    
INSERT 0 1
               
postgres=# insert into classes3 values(null,'joseph high school','');
ERROR:  null value in column "class_no" violates not-null constraint
DETAIL:  Failing row contains (null, joseph high school, ).

postgres=# select * from classes3;
 class_no |    school_name     | student_name 
----------+--------------------+--------------
        1 | joseph high school | nijam
        1 | joseph high school | nijam
        6 | joseph high school | 
        9 | joseph high school | null
        9 | joseph high school | 
(5 rows)

when you try to update the null values on class_no column you will get recieve error from following example i explained it.

postgres=# update classes3 set class_no=null;
ERROR:  null value in column "class_no" violates not-null constraint
DETAIL:  Failing row contains (null, joseph high school, nijam).                                            

PRACTICAL 4.POSTGRESQL NOT NULL CONSTRAINT CREATION USING CHECK METHOD:

Here null value cannot accepted on name column but email column will be accepted

postgres=# CREATE TABLE DATA (
    ID integer,
    name text NOT NULL,
    Email varchar,
    CONSTRAINT username_email_notnull CHECK (name IS NOT NULL AND email IS NULL)
);

postgres=# \d data
          Table "public.data"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | 
 name   | text              | not null
 email  | character varying | 
Check constraints:
    "username_email_notnull" CHECK (name IS NOT NULL AND email IS NULL)

CREATE TABLE

PRACTICAL 5.DROPPING NOT NULL CONSTRIANT:

postgres=# \d data
          Table "public.data"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | 
 name   | text              | not null
 email  | character varying | 
Check constraints:
    "username_email_notnull" CHECK (name IS NOT NULL AND email IS NULL)

--Here we are going to dropping name column not null constraint

postgres=# alter table data alter column name drop not null;
ALTER TABLE

--Check the data table for not null constraint whether dropped or not

postgres=# \d data
          Table "public.data"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | 
 name   | text              | 
 email  | character varying | 
Check constraints:
    "username_email_notnull" CHECK (name IS NOT NULL AND email IS NULL)