NOT NULL Constraint in 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
ConstraintsDefinition Level
NOT NULLCOLUMN
UNIQUECOLUMN, TABLE
PRIMARY KEYCOLUMN, TABLE
FOREIGN KEYCOLUMN, TABLE
CHECKCOLUMN, TABLE
NOT NULL Constraint in Oracle: Definition level

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';
NOT NULL Constraint in Oracle : output
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';
NOT NULL Constraint in Oracle : output
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';
NOT NULL Constraint in Oracle : output
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';
NOT NULL Constraint in Oracle : output

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
      );
NOT NULL Constraint in Oracle : output

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;
NOT NULL Constraint in Oracle : output

So its not allowing to update the JOB_ID to NULL.

RELATED TOPICS: