What is Not Null constraint in oracle
Not Null constraints in oracle are used to enforce not null values in the column in the table. So if you try to insert null into that column, it will throw an error.
How to create not null constraint
Not null constraints are defined at the table creation or you can modify the table, later on, to have a column changed to Not null. Let’s 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 constraints 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 constraints are also defined as check constraints and have search conditions as column name not null
Adding Not constraint after table creation
We can modify 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'
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 a 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
oracle alter table modify column type
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11554