Search code examples
sqloracle-databaseoracle11g

Error ORA-01400 while executing request INSERT INTO

I have 2 tables ISSUENCE and SKLAD.

In the ISSUENCE table, I am trying to make a request to insert the name of an item from the SKLAD table by its ID. Values entered in the table ISSUENCE:

ISSUE_ID, EPLOYEES_EMP_ID, SKLAD_ITEM_ID

Query used:

INSERT INTO issuence (issue_item_name) 
    SELECT item_name 
    FROM sklad, issuence 
    WHERE item_id = sklad_item_id;

But I am getting this error:

ORA-01400: cannot insert NULL into ("SKLAD"."ISSUENCE"."ISSUE_ID")

The tables look like this:

ISSUENCE:

enter image description here

SKLAD:

enter image description here

Solution

  • You didn't post all information regarding that schema, but - looking at screenshots you posted - it seems that item_id is a primary key in sklad table which is then referenced by sklad_item_id column from issuence. Also, issue_id is probably primary key column in issuence. Irrelevant in this case, employees_emp_id looks like yet another foreign key column.

    As you're on 11g, primary key columns aren't identity columns and their value can't be generated automatically in a declarative manner. It means that you either have to provide primary key values while inserting rows into tables, or use database triggers which will do that.

    Create tables:

    SQL> create table sklad
  2  (item_id           integer constraint pk_sklad primary key,
  3   item_name         varchar2(500),
  4   form_size         varchar2(10),
  5   item_quantity     integer
  6  );

Table created.

SQL> create table issuence
  2  (issue_id          integer constraint pk_iss primary key,
  3   employees_emp_id  integer not null,
  4   sklad_item_id     integer constraint fk_iss_sklad references sklad (item_id) not null,
  5   issue_date        date,
  6   issue_period_days integer,
  7   issue_quantity    integer,
  8   issue_item_name   nvarchar2(200)
  9  );

Table created.

    Sequence will be used for primary key columns:

    SQL> create sequence seq_is;

Sequence created.

    Sample sklad row:

    SQL> insert into sklad (item_id, item_name) values (seq_is.nextval, 'Some name');

1 row created.

    Here's where you got an error: you must provide values to NOT NULL columns, one way or another (trigger or manually). Here's an example:

    SQL> insert into issuence (issue_id, employees_emp_id, sklad_item_id)
  2  select seq_is.nextval, 100, s.item_id
  3  from sklad s
  4  where s.item_id = (select min(a.item_id)
  5                      from sklad a);

1 row created.

SQL> select * from issuence;

  ISSUE_ID EMPLOYEES_EMP_ID SKLAD_ITEM_ID ISSUE_DATE ISSUE_PERIOD_DAYS ISSUE_QUANTITY ISSUE_ITEM
---------- ---------------- ------------- ---------- ----------------- -------------- ----------
         2              100             1

SQL>