ORA 01400 and ORA 02296 : Cannot insert null or modify added column properties to NOT NULL

Question

"Modify your query to add a column that subtracts the old salary from the new salary. Label the column Increase. Run the revised query."

Well, as per my interpretation, I first attempted to add the column by scripting:

ALTER TABLE EMPLOYEES ADD (
    INCREASE2   NUMBER(6));

Then:

INSERT INTO EMPLOYEES(INCREASE2)
SELECT (salary*1.155) - salary FROM EMPLOYEES;

Error de SQL: ORA-01400: não é possível inserir NULL em ("HR"."EMPLOYEES"."EMPLOYEE_ID") 01400. 00000 - "cannot insert NULL into (%s)"

"HR"."EMPLOYEES"."EMPLOYEE_ID" is the primary key.

1. I'm not trying to insert a NULL value;

2. I don't know why oracle isn't accepting my entries. I tried to check whether there was any syntax errors in my expression by performing basic insert:

   INSERT INTO EMPLOYEES(INCREASE2)
   VALUES ('whatever');
   

And still I got the error.

I tried then modifying the column to not null

ALTER TABLE EMPLOYEES
MODIFY
(INCREASE2  NUMBER(6) NOT NULL);

And:

02296 00000 - "cannot enable (%s.%s) - null values found"
*Cause: an alter table enable constraint failed because the table
contains values that do not satisfy the constraint.
*Action: Obvious

I found a simple solution for the exercise, but still I am curious about why my code didn't succeed.

Resolution:

SELECT employee_id, last_name, salary,
    ROUND(salary * 1.155, 0) "New Salary",
    ROUND(salary * 1.155, 0) - salary "Increase"
FROM employees;

Answer 1

Your code didn't succeed because the column employees.employee_id is a non-null field without a default value. When you run:

INSERT INTO EMPLOYEES(INCREASE2)
    VALUES ('whatever');

The values of all the other fields in Employees are assigned the default, or NULL if no default value exists. Because this violates a constraint, you get an error.

Normally, a field like employee_id would be assigned to a sequence. This would automatically insert an auto-incremented value for each new record.