Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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)
);
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)



Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL