违反完整性约束-调用存储过程时未找到父项

Question

我试图在Oracle 11g XE中html" target="_blank">执行存储包过程调用，但由于某些原因，我得到以下错误：

错误报告-ORA-02291：完整性约束（ROOT。SYS_C007057）违反-未找到父密钥ORA-06512：在"ROOT。BOOKS_STORE"，第69行ORA-06512：第2行

02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"

*原因：外键值没有匹配的主键值。

*操作：删除外键或添加匹配的主键。

调用以下过程时：

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

该过程所做的是将数据插入books表中。以下是程序文本（位于books\u store包内）和books表的说明。

  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;

书籍描述：

 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)

因此，错误表示我的主键或外键有问题。虽然，我不明白到底是什么错了。

我认为问题是我传递了错误的author_id和publisher_id作为过程的参数，但它们是正确的。下面是对作者和发布者表的Select*调用：

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

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

你能帮我找出我的代码有什么问题以及如何使它工作吗？

Answer 1

我认为，问题可能在这里：

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

因为表中的“说明”列有另一个顺序：

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

尝试显式指定列名：

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);

现在看起来您正在将AUTHOR\u ID值插入PRICE列，PUBLISHER\u ID插入AUTHOR\u ID并将PRICE插入PUBLISHER\u ID。