SQL Error: ORA-02298: cannot validate (SYSTEM.AERO

问题:

I'm getting the following error in on Oracle SQL Developer:

Error starting at line 1 in command:
    ALTER TABLE AEROPUERTO ADD CONSTRAINT AEROPUERTO_FK FOREIGN KEY (CODIGO_CIUDAD) REFERENCES CIUDAD(CODIGO)

Error report:
    SQL Error: ORA-02298: cannot validate (SYSTEM.AEROPUERTO_FK) - 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

Why?

回答1:

There are records in AEROPUERTO that point to records that do not exist in CIUDAD.

To find out which records of AEROPUERTO have that kind of issue:

select * from AEROPUERTO where CODIGO_CIUDAD not in (select CODIGO from CIUDAD)

If the result set is not empty, you do have orphanaged records. You'll need to add the missing CIUDAD records in order to create the AEROPUERTO_FK foreign key, or update all the erroneous AEROPUERTO.CODIGO_CIUDAD to null (if this is a nullable field, but you will lose the city information for those airport records).

回答2:

remove primary key constraint from the column in which you want to add the foreign key