How can I fix, ORA-02291: integrity constant violated - parent key not found

Question

I'm using apex oracle, and I keep getting this violation. If anyone could explain to me why, I would be very grateful.

my code:

CREATE TABLE books
(
    book_id        INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
    title          VARCHAR(255) NOT NULL, 
    total_pages    INT NULL, 
    rating         DECIMAL(4, 2) NULL, 
    isbn           VARCHAR(13) NULL, 
    published_date DATE NULL, 
    publisher_id   INT NULL, 

    PRIMARY KEY(book_id),
    CONSTRAINT fk_publisher 
        FOREIGN KEY(publisher_id) REFERENCES publisher(publisher_id)
);

CREATE TABLE authors
(
    author_id   INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    first_name  VARCHAR(100) NOT NULL, 
    middle_name VARCHAR(50) NULL, 
    last_name   VARCHAR(100) NULL,

    PRIMARY KEY(author_id)
);

CREATE TABLE publisher
(
    publisher_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
    name         VARCHAR(255) NOT NULL,

    PRIMARY KEY(publisher_id)
)

I keep getting this error:

ORA-02291: integrity constraint (OANYANWU.FK_PUBLISHER) violated - parent key not found
ORA-06512: at "SYS.DBMS_SQL", line 1721"

What I'm trying to insert:

insert into books (book_id, title, total_pages, rating, isbn, published_date, publisher_id)
values (2, 'Facing the Intelligence Explosion', 91, 3.87, null, '01-02-2013', 109);

Side note: I have had one successful insert, and it was:

insert into books (book_id, title, total_pages, rating, isbn, published_date, publisher_id)
values (1, 'Lean Software Development: An Agile Toolkit', 240, 4.17, '9780320000000', '05-18-2003',5);

so I don't know why I'm getting this error!?

Answer 1

Here is your current failing insert:

INSERT INTO books (book_id, title, total_pages, rating, isbn,
                   published_date, publisher_id)
VALUES (2, 'Facing the Intelligence Explosion', 91, 3.87, NULL,
        '01-02-2013', 109);

This insert would only work if a publisher with id 109 already exists in the publisher table. I'm guessing that it doesn't, hence the error. You may verify this with the following query:

SELECT *
FROM publisher
WHERE publisher_id = 109;

If this query turns up an empty result set, then you have your answer.