• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » not null constraint in oracle

not null constraint in oracle

February 19, 2020 by techgoeasy Leave a Comment


Table of Contents

  • What is Not Null constraint in oracle
  • How to create not null constraint
  • How to drop the Not Null constraint
  • How to add not null constraint on the column containing null values

What is Not Null constraint in oracle

Not Null constraint in oracle are used to enforce not null values in the column in the table. So if you try to insert null into those column, it will throw error.

How to create not null constraint

Not null constraints are defined at the table creation or you can latter on modify the table to have a column changed to Not null .Lets take a look at the examples.

Table creation
Not null constraint is specified inline with the column.

SQL> CREATE TABLE "EMP"
    ("EMPNO" NUMBER(4,0) Not Null,
     "ENAME" VARCHAR2(10) Not null,
     "JOB" VARCHAR2(9),  
     "MGR" NUMBER(4,0),       
     "HIREDATE" DATE,
     "SAL" NUMBER(7,2) ,  
     "COMM" NUMBER(7,2),
     "DEPTNO" NUMBER(2,0)
      );

 Table created. 

Here we have defined two not null constraint on the table emp. We can check the Not null constraints on the table using the below query

SQL> column CONSTRAINT_NAME format a20
SQL> column SEARCH_CONDITION format a50
SQL> SELECT Constraint_name, Search_condition
     FROM User_constraints
     WHERE Table_name = 'EMP'  AND    
     Constraint_type = 'C';
not null constraint in oracle

Not Null constraints are also defined as check constraints and have search condition as column name not null

Adding Not constraint after table creation

We can modify the table the table to add the not null constraint

SQL> alter table emp modify ("HIREDATE" not null);
 Table altered.

If we have null data in the column existing then this statement will fail

SQL> SELECT Constraint_name, Search_condition
     FROM User_constraints
     WHERE Table_name = 'EMP'  AND
     Constraint_type = 'C'
check not null constraint in oracle

We can give the custom name to the Not Null constraint also. Here is the example to demonstrate it

SQL> CREATE TABLE "EMP"
 ("EMPNO" NUMBER(4,0) Constraint EMP_NOTNULL Not Null,  
 "ENAME" VARCHAR2(10) Not null,
 "JOB" VARCHAR2(9),
 "MGR" NUMBER(4,0),
 "HIREDATE" DATE,
 "SAL" NUMBER(7,2) ,
 "COMM" NUMBER(7,2),
 "DEPTNO" NUMBER(2,0)
  );

 Table created.
 SQL> column CONSTRAINT_NAME format a20
 SQL> column SEARCH_CONDITION format a50
 SQL> SELECT Constraint_name, Search_condition
     FROM User_constraints
     WHERE Table_name = 'EMP'  AND
     Constraint_type = 'C';
 SQL> alter table emp modify ("HIREDATE" Constraint H_NOTNULL  Not    Null);
 
Table altered.
 SQL> SELECT Constraint_name, Search_condition
      FROM User_constraints
      WHERE Table_name = 'EMP'  AND
      Constraint_type = 'C';

How to drop the Not Null constraint

We need to modify the column to accept null values

SQL>alter table emp modify "HIREDATE" Null;

Table altered.

SQL> SELECT Constraint_name, Search_condition
     FROM User_constraints
     WHERE Table_name = 'EMP'  AND
     Constraint_type = 'C';

We can drop the not null constraint using the alter table drop constraint command also

SQL> alter table emp drop constraint  EMP_NOTNULL ;
Table altered;

SQL> SELECT Constraint_name, Search_condition
     FROM User_constraints
     WHERE Table_name = 'EMP'  AND
     Constraint_type = 'C';

How to add not null constraint on the column containing null values

If you try to add not null constraint on the column containing null values ,then that command will fail as it will validate all the rows all the existing rows for not null constraint. Sometimes we want to keep the existing null values and check for future rows that they contain a not null value for this column, then in that case, we may enable the constraint with novalidate clause. With this clause, it will not check the existing rows, but future rows will be checked

alter table emp modify "HIREDATE" not Null novalidate;

Also Read
drop primary key oracle
Foreign key in oracle
Unique Key constraint in Oracle
how to check all constraints on a table in oracle
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11554


Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Oracle Indexes and types of indexes in oracle with example
  • Top 30 Most Useful Concurrent Manager Queries
  • Top 46 Oracle ASM Interview Questions
  • Oracle dba interview questions and answers
  • 40 question you must know about R12.2



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • EBS PDB service name disappear from listener in 19c
  • how to download oracle e-business suite 12.2 software
  • Step by step upgrade process to R12.2 Upgrade part -2(Main Upgrade Driver for R12.2.0)
  • Step by step upgrade process for R12.2 Upgrade Part -1
  • Step by step upgrade process for R12.2 Upgrade Part -4(Applying 12.2.x Release Update Pack)

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us