Integrity constraint violated - parent key not found when calling stored procedure

Question

I'm trying to perform the stored package procedure call in Oracle 11g XE, but for some reason I get the error below:

Error report - ORA-02291: integrity constraint (ROOT.SYS_C007057) violated - parent key not found ORA-06512: at "ROOT.BOOKS_STORE", line 69 ORA-06512: at line 2
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.

*Action: Delete the foreign key or add a matching primary key.

when calling the following procedure:

begin
  books_store.add_books_to_store(
    'To Kill a Mockingbird', 21,
    'test description', 5,
    'https://test_img.jpg',
    10, 6.99
  );
end;

What the procedure does is insert the data into the books table. Below is the procedure text (which is inside the books_store package) and the description of the books table.

  procedure add_books_to_store(
    book_name books.name%type, book_author_id books.author_id%type,
    book_description books.description%type default null,
    book_publisher_id books.publisher_id%type, book_cover_img books.cover_img%type,
    books_count books.available_count%type, book_price books.price%type)
    is
    existing_books_count integer;
    add_negative_or_zero_books exception;
    begin
      if books_count <= 0 then
        raise add_negative_or_zero_books;
      end if;

      select count(*) into existing_books_count from books
      where
        name = book_name and author_id = book_author_id and
        description = book_description and publisher_id = book_publisher_id and
        price = book_price;

      if existing_books_count = 0 then
        insert into books values (books_seq.nextval, book_name, book_description,
          book_cover_img, books_count, book_author_id, book_publisher_id, book_price);
      else
        update books set available_count = available_count + books_count
          where
            name = book_name and author_id = book_author_id and
            description = book_description and publisher_id = book_publisher_id and
            price = book_price;
      end if;

      exception
        when add_negative_or_zero_books then
          raise_application_error(-10003, 'You cannot add 0 or less books');
    end add_books_to_store;

books description:

 DESC books;
 Name                   Null?        Type
 ------------------------------------------------------------
 ID                    NOT NULL     NUMBER(5)
 NAME                  NOT NULL     VARCHAR2(200)
 DESCRIPTION                        VARCHAR2(2000)
 COVER_IMG                          VARCHAR2(300)
 AVAILABLE_COUNT       NOT NULL     NUMBER(4)
 PRICE                              NUMBER(10,2)
 AUTHOR_ID                          NUMBER(5)
 PUBLISHER_ID                       NUMBER(5)

So, the error says that there's something wrong with my primary or foreign keys. Though, I cannot understand what exactly is wrong.

I thought that the problem was that I passed wrong author_id and publisher_id as arguments to the procedure, but they're correct. Here's the select * calls for authors and publishers tables:

select * from authors;
    ID FIRST_NAME       LAST_NAME        BIRTHDAY
--------------------------------------------------
    21   Harper           Lee            28-APR-26

select * from publishers;
    ID            NAME
---------------------------
     5      Penguin Fiction

Could you help me to find out what's wrong with my code and how to make it work?

PS: Here's my ER-diagram:

Try to desc the violated foreign key; this will say you which column(s) you are populating with a wrong value — Aleksej, Nov 24 '16 at 21:09
@Aleksej I'm not sure what you mean. Could you please be more specific on what command I should execute? — Denis Yakovenko, Nov 24 '16 at 21:13
He means `select * from all_constraints c where c.owner = 'ROOT' and c.constraint_type = 'SYS_C007057'` — William Robertson, Nov 25 '16 at 12:40
The error is telling you that you tried to add a BOOK with an author or publisher that didn't exist (the definition for SYS_C007057 will tell you which column). For example, say you have authors 1, 2 and 3, and then you try to insert a book with author = 42. — William Robertson, Nov 25 '16 at 12:46

score 3 · Accepted Answer · answered Nov 24 '16 at 21:29

I think, problem could be here:

  insert into books values (books_seq.nextval, book_name, book_description,
      book_cover_img, books_count, book_author_id, book_publisher_id, book_price);

Because in table description columns have another order:

PRICE                              NUMBER(10,2) 
AUTHOR_ID                          NUMBER(5)
PUBLISHER_ID                       NUMBER(5)

Try to specify column names explicitly:

insert into books (ID, NAME, DESCRIPTION, COVER_IMG, AVAILABLE_COUNT, PRICE, AUTHOR_ID, PUBLISHER_ID)
values (books_seq.nextval, book_name, book_description,
      book_cover_img, books_count, book_price, book_author_id, book_publisher_id);

Now it looks like you are inserting AUTHOR_ID value into PRICE column, PUBLISHER_ID into AUTHOR_ID and PRICE into PUBLISHER_ID.

Omg, thank you! I would never have noticed. From here on I'll always specify the column names explicitly — Denis Yakovenko, Nov 24 '16 at 21:37

Integrity constraint violated - parent key not found when calling stored procedure

PS: Here's my ER-diagram:

1 Answers1