Sayantan's Blog On Oracle

NOT NULL Constraint in Oracle

Constraints in Oracle

• Constraints allows Oracle to set some rule at table or column level
• It restricts us to do DML operations if there is any dependency in data

Types of constraints:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

Constraints	Definition Level
NOT NULL	COLUMN
UNIQUE	COLUMN, TABLE
PRIMARY KEY	COLUMN, TABLE
FOREIGN KEY	COLUMN, TABLE
CHECK	COLUMN, TABLE

We will discuss about NOT NULL constraint in this article.

NOT NULL constraint restricts us to enter null value in a column. It can be defined at column level only.

Types of constraint definition:

• In-line definition
• Out-of-line definition

In-line definition:

If we define the constraint during table creation then its called in-line constraint definition.

CREATE TABLE EMP_NOT_NULL_TAB
(EMPLOYEE_ID     NUMBER(6) NOT NULL,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10) NOT NULL,
 SALARY          NUMBER(8, 2)
);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_NOT_NULL_TAB';

SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_NOT_NULL_TAB';

Please note that during creation we haven't mentioned the name of the NOT NULL constraint. So oracle has given a system defined name.
We can provide an user defined name.

DROP TABLE EMP_NOT_NULL_TAB;

CREATE TABLE EMP_NOT_NULL_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMPID_NOT_NULL_CONS NOT NULL,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10) CONSTRAINT JOB_ID_NOT_NULL_CONS NOT NULL,
 SALARY          NUMBER(8, 2)
);

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_NOT_NULL_TAB';

SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_NOT_NULL_TAB';

Out-of-line definition:

ALTER TABLE EMP_NOT_NULL_TAB MODIFY(LAST_NAME VARCHAR2(30) CONSTRAINT LAST_NAME_NOT_NULL_CONS  NOT NULL);

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_NOT_NULL_TAB';

SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_NOT_NULL_TAB';

Disabling NOT NULL Constraint:

ALTER TABLE EMP_NOT_NULL_TAB DISABLE CONSTRAINT LAST_NAME_NOT_NULL_CONS;

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_NOT_NULL_TAB';

ALTER TABLE EMP_NOT_NULL_TAB ENABLE CONSTRAINT LAST_NAME_NOT_NULL_CONS;

Enabling NOT NULL Constraint:

ALTER TABLE EMP_NOT_NULL_TAB ENABLE CONSTRAINT LAST_NAME_NOT_NULL_CONS;

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_NOT_NULL_TAB';

DROP NOT NULL Constraint:

ALTER TABLE EMP_NOT_NULL_TAB DROP CONSTRAINT LAST_NAME_NOT_NULL_CONS;

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_NOT_NULL_TAB';

Impact of NOT NULL Constraint on DML operations:

INSERT:

We are trying to insert one record in EMP_NOT_NULL_TAB table with JOB_ID as NULL.

INSERT INTO EMP_NOT_NULL_TAB(EMPLOYEE_ID,
                             FIRST_NAME,
                             LAST_NAME,
                             JOB_ID,
                             SALARY
                            )
VALUES(100,
       'John',
       'Smith',
       NULL,
       1000
      );

So it is not allowing to enter record with JOB_ID as NULL.

INSERT INTO EMP_NOT_NULL_TAB(EMPLOYEE_ID,
                             FIRST_NAME,
                             LAST_NAME,
                             JOB_ID,
                             SALARY
                            )
VALUES(100,
       'John',
       'Smith',
       'IT_PROG',
       1000
      );

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       SALARY 
FROM EMP_NOT_NULL_TAB; 

UPDATE:

We are trying to update the JOB_ID column of above example to NULL.

UPDATE EMP_NOT_NULL_TAB
SET JOB_ID = NULL
WHERE EMPLOYEE_ID = 100;

So its not allowing to update the JOB_ID to NULL.

RELATED TOPICS:

• Insert operation in Oracle
• Multiple Table Insert in Oracle