integrity constraint violated - parent key not found when the value exists

Question

CREATE TABLE Film
(
    idFilm        NUMBER         GENERATED BY DEFAULT as IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
    titre         VARCHAR2(100)  NOT NULL,
    anneeDeSortie NUMBER(4)      NOT NULL,
    langueOrigine VARCHAR2(25),
    duree         Number(3)      NOT NULL,
    resume        VARCHAR2(600)  NOT NULL,
    affiche       VARCHAR2(2083),
    realisateur   number,
    constraint fk_idRealisateur
        FOREIGN KEY (realisateur) REFERENCES Personnalite (idPersonnalite)
);

That has the following row:

SELECT * FROM film where film.idfilm = 164184;

and when I try to add a row to the table Scenariste:

CREATE TABLE Scenariste
    (
    idScenariste  NUMBER GENERATED ALWAYS as IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
    nom           VARCHAR2(100)  NOT NULL,
    idFilm        NUMBER         NOT NULL,
    constraint fk_idScenariste
        FOREIGN KEY (idScenariste) REFERENCES Film (idFilm)
);

INSERT INTO scenariste (nom, idFilm) VALUES ('Paul Attanasio', 164184);

I get the following error:

Error starting at line : 3 in command -
INSERT INTO scenariste (nom, idFilm) VALUES ('Paul Attanasio', 164184)
Error report -
ORA-02291: integrity constraint (EQUIPE106.FK_IDSCENARISTE) violated - parent key not found

Why is this is happening if the film row with id 164184 actually exists?

Answer 1

You've defined your table with a wrong FK constraint.

You have:

CREATE TABLE Scenariste
(
    idScenariste  NUMBER GENERATED ALWAYS as IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
    nom           VARCHAR2(100)  NOT NULL,
    idFilm        NUMBER         NOT NULL,
    CONSTRAINT fk_idScenariste
        FOREIGN KEY (idScenariste) REFERENCES Film (idFilm)
);

but I believe the FK constraint ought to be using the idFilm (not the idScenariste column):

    CONSTRAINT fk_idScenariste
        FOREIGN KEY (idFilm) REFERENCES Film (idFilm)