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?
>Solution :
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)