Forum Stats

  • 3,734,276 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

Reg: validated Praent key not found when enabling the constraints.

chandra_1986
chandra_1986 Member Posts: 262 Blue Ribbon

HI Experts,

While enabling the constraints getting below error:

alter table RES enable constraint FK_COMP; 

 alter table RES enable constraint FK_COMP 

                    * 

ERROR at line 1: 

ORA-02298: cannot validate (MVP_DM.FK_COMP) - parent keys not found

Best Answers

Answers

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    Thanks experts,


    Got below results:


    select * from all_constraints t where t.owner='MVP_DM' and t.constraint_name='FK_COMP';



    OWNER                                                              CONSTRAINT_NAME             C TABLE_NAME                                                            SEARCH_CONDITION

    -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- - -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------

    SEARCH_CONDITION_VC

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    R_OWNER                                                             R_CONSTRAINT_NAME            DELETE_RU STATUS  DEFERRABLE   DEFERRED VALIDATED   GENERATED    BAD RELY LAST_CHAN INDEX_OWNER

    -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------- -------- -------------- --------- ------------- -------------- --- ---- --------- --------------------------------------------------------------------------------------------------------------------------------

    INDEX_NAME                                                            INVALID VIEW_RELATED  ORIGIN_CON_ID

    -------------------------------------------------------------------------------------------------------------------------------- ------- -------------- -------------

    MVP_DM                                                              FK_COMP              R RESULT


    MVP_DM                                                              PK_COMP              NO ACTION DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME        14-JAN-21

                       




    ===================================



    SQL> select * from all_cons_columns t where t.coNSTRAINT_NAME='PK_COMP' and t.owner='MVP_DM';


    OWNER                                                              CONSTRAINT_NAME             TABLE_NAME

    -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

    COLUMN_NAME

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     POSITION

    ----------

    MVP_DM                                                              PK_COMP              COMPONENT

    COMPONENT_KEY





    ================================================



    SQL> select * from all_cons_columns t where t.owner='MVP_DM' and t.constraint_name='FK_COMP';


    OWNER                                                              CONSTRAINT_NAME             TABLE_NAME

    -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

    COLUMN_NAME

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     POSITION

    ----------

    MVP_DM                                                              FK_COMP              RESULT

    COMPONENT_KEY

  • RogerT
    RogerT Member Posts: 1,852 Gold Trophy
    create table exceptions(row_id rowid,
    owner varchar2(30),
    table_name varchar2(30),
    constraint varchar2(30));
    
    alter table RES enable constraint FK_COMP exceptions into exceptions;
    

    afterwards you'll find the rowids of the rows that prevent enabling the constraint in the exceptions table (row_id column).

    hth

    cormaco
  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    thanks experts, i got these many records in that:


    SQL> select count(*) from exceptions;


     COUNT(*)

    ----------

     63336231


    How to get resolved from this error:

  • BEDE
    BEDE Oracle Developer Member Posts: 2,234 Gold Trophy

    So, there are awfully many rows violating that constraint...

    You could get those rows into a table like:

    create table fk_comp_violated as

    select *

    from res

    where res.rowid in (select x.rowid from exceptions x)

    ;

    Then delete those rows from the res table:

    delete from res where res.rowid in (select x.rowid from exceptions x);

    Then enable the constraint.

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    I have impoted tables data with content=DATA_ONLY from source, so if i delete the data it will impact the table contents rite to enable the constraints.

    Can we copy metadata with constraints from source and import it into target side.

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    OK thanks expert,


    so these data which is inserted will not be validated by constraints, but next data which we inputs in these tables those data will be validated correct, by using enable novalidate constraints option.

  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    Regarding your question:

    enable NOVALIDATE will work - but is that your business requirement? Pretend that you fixed it, and don't worry about the past? What if the same reason that caused so many child rows not to have a parent will cause similar issues in the future? It won't, since you have the constraint enabled - but you may get into much bigger issues, like an insert of three million rows being thrown out because it violates the constraint.

    Did you look at a few of the "exception" rows to see what the COMPONENT_KEY looks like? If the values look legitimate, you may want to find out why the same values don't exist in the parent table.

    You may also find some weird things. For example: the foreign key value is allowed to be NULL. (Which, by the way, seems to me like a better solution than one proposed earlier: If you want to VALIDATE the constraint, which means you need to fix the existing issue, and you can't find a better solution, rather than deleting all the "exception" rows, you could simply UPDATE them to give them NULL component key.) However, in some organizations, they don't allow NULL in string columns; they intercept those and replace them with a single space. A completely idiotic practice if you ask me, but I've seen it more than once. In that case, unless the parent table also has a row with a single space for COMPONENT_KEY, the validation will fail.

    This is just one example of what you might find if you were curious enough to find out WHY you have exception rows (let alone many millions of exception rows). Regardless, before you enable NOVALIDATE, you might want to discuss with your business users, to see if that will be OK for them.

  • mathguy
    mathguy Member Posts: 9,779 Gold Crown
    edited Jan 18, 2021 3:52PM

    Ummm.... Thank you for accepting my suggestion about BEDE's answer.

    My own answer, where I commented on "accepted answers", is not an answer to your original question. You didn't need to mark my reply as an "accepted answer". I am not sure if you are able to undo that action, but if you can, that would be a good thing.

    Cheers - mathguy

Sign In or Register to comment.