Add NOT NULL Constraint in Oracle

Today I will explain about the different ways to add a NOT NULL constraint on a column.

Advantages of NOT NULL constraint:
1) You can prevent users to enter null values into this column.
2) The pre-requisite to enforce Primary Key on a Column.

Now Lets get into the details:
There are two ways which i can think of
1) While creating the table using CREATE TABLE statement.
2) Modifiying the table definition using ALTER TABLE statement.

CREATE TABLE:
CREATE TABLE DEPT(DEPTNO NUMBER NOT NULL, DNAME VARCHAR2(30))

When you are creating a table as part the Column Definition you can specify the Constraint. This is very easy. But most of the cases we get into a position where we have a table in PROD or some where and want to define a Not Null constraint on the column.

ALTER TABLE:

Created a table called Dept to store Department information.

CREATE TABLE DEPT(DEPTNO NUMBER, DNAME VARCHAR2(30));

ALTER TABLE DEPT MODIFY DEPTNO NOT NULL;

That's it. Now if you use any of following queries you can see the column is now not nullable.

SELECT column_name,NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DEPT'

or

DESC DEPT