Home

Insert statement values not including Non-Null Columns

Rule description

  • Insert statements should include values for non-null columns

 

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;
Visual Expert 2020
 VEPLSQLRULE3