Ошибка ora 02298

One of the classic questions…

But I really can’t find where the issue is in the parent table. I am currently working on a mini-version of the IMDB database. Our teacher tasked us with taking the original database we were given and make it into a smaller, and smarter version. It should follow this diagram:

I have so far successfully made the COMPANY_NAME, MOVIE_COMPANY, MOVIE, GENRE and PLOT tables and is currently making KEYWORD. The table itself now exists, but I cannot place the Foreign constraint which indicates that the MOVIE_ID present in keyword is from the parent table MOVIE. I keep getting the following error:

ALTER TABLE KEYWORD
  ADD CONSTRAINT FK_MOVIE_ID_KEYWORD
    FOREIGN KEY (MOVIE_ID)
    REFERENCES MOVIE (ID)
Error report -
SQL Error: ORA-02298: cannot validate (DB_031.FK_MOVIE_ID_KEYWORD) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed because the table has
           child records.
*Action:   Obvious <-- especially this part is annoying

I have looked around at similar questions and then checked my statement a few times, but I have so far been unable to see why this fails. The GENRE and PLOT tables are both empty in terms of data, as they are made for future expansion of the database. But KEYWORD already have a collection of KEYWORD, MOVIE_ID pairs.

EDIT: Request Schemas

Find the row which causing violation of foreign key constraint in Oracle

Find the row which caused a violation of the foreign key when tried to enable the foreign key constraint:

To clean the rows that violate the foreign key constraint, You need to create an EXCEPTIONS table in the current schema. Use this script present in the RDBMS folder of Oracle Home: rdbms/admin/utlexcpt.sql.

Following are the steps to find the row that violate the foreign key constraint:

1. Create an exceptions table.

SQL> @?/rdbms/admin/utlexcpt.sql;

2. Populate the EXCEPTIONS table with the rows that violate the constraint, using the EXCEPTIONS INTO clause.

SQL> alter table table_name modify constraint fk_constraint_name validate exceptions into exceptions;

Note This statement while executing still throws the ORA-02298 error as long as there are rows that violate the constraint. The statement also inserts records into the EXCEPTIONS table for any bad rows.

1. Check the Exception table for rows.
   You use the ROW_ID column of the EXCEPTIONS table to remove any records that violate the constraint. In Example, you find that only one row needs to be removed from the EMP table.

 SQL> SELECT * FROM EXCEPTIONS;
Example:
ROW_ID              OWNER  TABLE_NAME  CONSTRAINT
------------------  -----  ----------  --------------------
AAAFKQAABAAAK8JAAB  SCOTT  EMP         EMP_DEPT_FK

Example of Creating Foreign key and reproducing the error and solution:

--- Created Parent p1 and Child c1 table with foreign key established 

SQL> create table p1(id number primary key, name varchar2(100));
Table created.

SQL> create table c1(cid number primary key, pid number, constraint fk_c2 foreign key (pid) references p1(id));
Table created.

-- insert into parent and child table
SQL> insert into p1 values (1,'a');
1 row created.
SQL> insert into p1 values (2,'b');
1 row created.

SQL> insert into c1 values (2,5);
insert into c2 values (2,5)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FK_C2) violated - parent key not found

-- Disable the foreign key constraint to insert value
SQL> alter table c2 disable constraint FK_C2;
Table altered.

SQL> insert into c2 values (2,5);
1 row created.

--Enable the constraint throw the error 
SQL> alter table c2 enable constraint FK_C2;
alter table c2 enable constraint FK_C2
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SYS.FK_C2) - parent keys not found

--Find the row causing problem to enable the constraint:
SQL> @?/rdbms/admin/utlexcpt.sql;

Table created.

--Run again the command using EXCEPTION table:
SQL> alter table c2 modify constraint FK_C2 validate exceptions into exceptions;
alter table c2 modify constraint FK_C2 validate exceptions into exceptions
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SYS.FK_C2) - parent keys not found

--Check the EXCEPTION table having rowid which causing problem:
SQL> col owner for a10
SQL> col table_name for a10
SQL> col constraint for a20
SQL> select * from exceptions;

ROW_ID             OWNER      TABLE_NAME CONSTRAINT
------------------ ---------- ---------- --------------------
AAATBmAABAAAIGBAAA SYS        C2         FK_C2


-- Check the row in table 
SQL> select * from c2 where rowid in (select row_id from exceptions);

       CID        PID
---------- ----------
         2          5