Home

Description

The "Insert statement values not including Non-Null Columns" rule in PL/SQL states that when inserting values into a table, any non-null columns must be included in the insert statement. This means that any columns with a non-null constraint must be included in the insert statement, even if the value being inserted is NULL. If a non-null column is not included in the insert statement, an error will be thrown. This rule is important to ensure data integrity and accuracy in the database.

Key Benefits

• Preserves Data Integrity: Inserting statement values without including non-null columns ensures that all data remains consistent and accurate, as no data is changed or deleted.
• Reduces Errors: By avoiding the need to update non-null columns, inserting statement values without including non-null columns reduces the risk of errors that could occur from manual updates.
• Saves Time: Inserting statement values including non-null columns eliminates the need to manually update non-null columns, which can save time and effort.

Non-compliant Code Example

Create TABLE EMPLOYEE
(
  EMP_ID number(10) NOT NULL,
  FIRSTNAME NVARCHAR2(75) NOT NULL,
  LASTNAME NVARCHAR2(75) NOT NULL,
  DEPT_ID int,  
  ADDRESS NVARCHAR2(500) NOT NULL,
  CONSTRAINT pk_employee PRIMARY KEY (EMP_ID)
);

BEGIN
	INSERT INTO	EMPLOYEE(EMP_ID,FIRSTNAME,ADDRESS) --Non compliant code (Insert clause does not include all not null columns of EMPLOYEE table)
	VALUES (3,'Demo','52 AAGMAN BUNGALOWS');
END;

Compliant Code Example

Create TABLE EMPLOYEE
(
  EMP_ID number(10) NOT NULL,
  FIRSTNAME NVARCHAR2(75) NOT NULL,
  LASTNAME NVARCHAR2(75) NOT NULL,
  DEPT_ID int,  
  ADDRESS NVARCHAR2(500) NOT NULL,
  CONSTRAINT pk_employee PRIMARY KEY (EMP_ID)
);

BEGIN
	INSERT INTO	EMPLOYEE(EMP_ID,FIRSTNAME,LASTNAME,ADDRESS) --Compliant code (Insert clause include all not null columns of EMPLOYEE table)
	VALUES (3,'Demo','Test','52 AAGMAN BUNGALOWS');
END;