ORA-01400: cannot insert NULL when inserting value into Oracle Database-CodePudding

When I try to insert into my employee table I'm getting an error:

INSERT INTO employee (department_id) VALUES (1)
Error report -
ORA-01400: cannot insert NULL into ("CYCLOPS"."EMPLOYEE"."ID")

Cyclops is my username that I've logged into the database as. I've created 2 tables. The employee table has a foreign key called department_id that refers to the department table id column.

Name          Null?    Type         
------------- -------- ------------ 
ID            NOT NULL NUMBER(5) -- That's the primary key   
LAST_NAME              VARCHAR2(20) 
SALARY                 NUMBER       
DEPARTMENT_ID          NUMBER -- That's the foreign key to the department id column

This is my department table

Name            Null?    Type         
--------------- -------- ------------ 
ID              NOT NULL NUMBER       
DEPARTMENT_NAME          VARCHAR2(20)

This is a select all on my employee table:

id  last_name   salary
1   JONES       20000   
2   SMITH       35000   
3   KING        40000   
4   SIMPSON     52000   
5   ANDERSON    31000

This is a select all from the department table:

ID  department_name
1   IT
2   HR
3   SALES

What I want to do is insert the department_id into the employee record so I can add him to a department. Why am I getting an error on this insert?

CodePudding user response：

What I want to do is insert the department_id into the employee record so I can add him to a department. Why am I getting an error on this insert?

You don't need to insert a new record, you need to update an existing record.

With INSERT INTO employee (department_id) VALUES (1) you create a new row where only the columns mentioned (in this case department_id) get values, all others are given null.

As your column ID is defined as NOT NULL, it throws an exception.

If you want to change your existing data, you need to update a record like

UPDATE employee SET department_id = 1 WHERE id = 1;
UPDATE employee SET department_id = 2 WHERE id = 3;

Which will get you

id  last_name   salary   department_id
1   JONES       20000    1
2   SMITH       35000    (null)
3   KING        40000    2
4   SIMPSON     52000    (null)
5   ANDERSON    31000    (null)

CodePudding user response：

To be honest, it says pretty clear what is happening.

The "employee" table has the ID non-nullable column

ID            NOT NULL NUMBER(5)

It means it's value can't be set to null

The command

INSERT INTO employee (department_id) VALUES (1)

will insert 1 to department_id and will try to populate every other column with null.

You need to provide id at least in order for making it work

INSERT INTO employee (id, department_id) VALUES (1, 1);