Why am I violating an integrity constraint? (ORA-02291)

When I try to alter one of my tables to set one of it’s rows as a foreign key, I get the following error: “ORA-02291: integrity constraint (SYSTEM.M_FRAN_FK) violated – parent key not found”

This error only occurs when I insert a row into the database with a none-null value. I’m trying to understand why it’s doing this and what exactly I’m doing wrong. Here’s the sql code;

    CREATE TABLE Movie(
    Movie_Title     VARCHAR2(30) CONSTRAINT movie_title_pk PRIMARY KEY,
    Screen_Time     Number(3),
    M_Reviews       NUMBER(2,1),
    Fran_Name       VARCHAR2(30));
    
    CREATE TABLE Franchise(
    Fran_Name       VARCHAR2(30) CONSTRAINT fran_name_pk PRIMARY KEY);

    ALTER TABLE Movie
    ADD CONSTRAINT m_fran_fk FOREIGN KEY(Fran_Name)
    REFERENCES Franchise(Fran_Name);
    
    --this is fine
    INSERT INTO Movie
    VALUES('Hubie Halloween', 102, 5.2, '');

    --this is also fine
    INSERT INTO Movie
    VALUES('Titanic', 194, 7.8, '');

    --this is NOT fine
    INSERT INTO Movie
    VALUES('Iron Man 2', 124, 7.0, 'Marvel: Iron Man');

Answer

You need the “Marvel: Iron Man” franchise to exist before you insert “Iron Man” into Movie.

Also, you should always use explicit column names in INSERT statements.

INSERT INTO Franchise ( Fran_Name )
VALUES ( 'Marvel: Iron Man' )

INSERT INTO Movie ( Movie_Title, Screen_Time, M_Reviews, Fran_name )
VALUES( 'Iron Man 2', 124, 7.0, 'Marvel: Iron Man' );

…and more controversially, I feel database tables should have plural names if the table represents an unbounded collection. _{(I look forward to the dozen or so comment replies from people who disagree 😉 )}