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

chandra_1986

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

BEDE

First see which table that constraint belongs to and to which primary key that refers:

select *

from all_constraints t

where t.owner='MVP_DM' t.constraint_name='FK_COMP'

;

The column r_constraint_name shows the name of the primary key referenced.

Then see what columns in what table that primary key has to do with:

select *

from all_cons_columns t

where t.cosntraint_name=&x_primary_key and t.owner=&r_owner

;

Then see what columns in the table the foreign key constraint is on refer the primary key columns:

select *

from all_cons_columns t

where t.owner='MVP_DM' t.constraint_name='FK_COMP'

;

And then you may get the rows that violate the FK with a select like below:

select *

from x_table_with_fk fkt

where not exists (select 1 from x_table_referenced rt where rt.col_x1=fkt.col_x1)

;

That admitting that the foreign key has only one column. If there are several columns, then there will be more conditions in the subquery like: and rt.col_x2=fkt.col_x2.

Then it matters what you do with the rows that violate the constraint.

You may delete them with a delete statement derived from the select getting the rows violating the constraint:

delete from x_table_with_fk fkt

where not exists (select 1 from x_table_referenced rt where rt.col_x1=fkt.col_x1);

After that delete you may enable that constraint.

Or, if you do not wish to delete those rows (at least not yet) and are still willing to enable the constraint so that no nwe rows validating that constraint may be inserted, ten you may:

alter table RES enable novalidate constraint FK_COMP; 

That's all I can tell having the info you have given so far.

Solomon Yakobson

Or, if you want to keep all existing rows and enforce FK from now on use:

alter table RES enable novalidate constraint FK_COMP; 
alter table RES enable novalidate constraint FK_COMP 

SY.

Paulzip

Your problem is not rocket science, this is simple DB concepts 101.

1. You've probably imported a load of records into RESULT after disabling the FK constraint FK_COMP.
2. The records have a COMPONENT_KEY column which has a FK link to table.column = COMPONENT.COMPONENT_KEY
3. Those values don't exist in COMPONENT.COMPONENT_KEY
4. Oracle cannot re-enable the FK constraint, because the FK is violated.

To fix, either delete those records from RESULT or insert the missing records for COMPONENT.COMPONENT_KEY

mathguy

In the interest of fairness, let me comment on your choice of "accepted answer". The very first answer you received was from BEDE. He explained how to go about finding out exactly which rows in the "child table" caused the violation, which is a step further than the answer from Paulzip. And the other answer you accepted, from Solomon Yakobson, only told you to enable NOVALIDATE. BEDE suggested exactly the same thing several replies before Solomon's answer. So, why is BEDE's answer not one of the "accepted answers"?

BEDE

First see which table that constraint belongs to and to which primary key that refers:

select *

from all_constraints t

where t.owner='MVP_DM' t.constraint_name='FK_COMP'

;

The column r_constraint_name shows the name of the primary key referenced.

Then see what columns in what table that primary key has to do with:

select *

from all_cons_columns t

where t.cosntraint_name=&x_primary_key and t.owner=&r_owner

;

Then see what columns in the table the foreign key constraint is on refer the primary key columns:

select *

from all_cons_columns t

where t.owner='MVP_DM' t.constraint_name='FK_COMP'

;

And then you may get the rows that violate the FK with a select like below:

select *

from x_table_with_fk fkt

where not exists (select 1 from x_table_referenced rt where rt.col_x1=fkt.col_x1)

;

That admitting that the foreign key has only one column. If there are several columns, then there will be more conditions in the subquery like: and rt.col_x2=fkt.col_x2.

Then it matters what you do with the rows that violate the constraint.

You may delete them with a delete statement derived from the select getting the rows violating the constraint:

delete from x_table_with_fk fkt

where not exists (select 1 from x_table_referenced rt where rt.col_x1=fkt.col_x1);

After that delete you may enable that constraint.

Or, if you do not wish to delete those rows (at least not yet) and are still willing to enable the constraint so that no nwe rows validating that constraint may be inserted, ten you may:

alter table RES enable novalidate constraint FK_COMP; 

That's all I can tell having the info you have given so far.

chandra_1986

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

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

chandra_1986

thanks experts, i got these many records in that:

SQL> select count(*) from exceptions;

 COUNT(*)

----------

 63336231

How to get resolved from this error:

BEDE

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

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.

Solomon Yakobson

Or, if you want to keep all existing rows and enforce FK from now on use:

alter table RES enable novalidate constraint FK_COMP; 
alter table RES enable novalidate constraint FK_COMP 

SY.

chandra_1986

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.

Paulzip

Your problem is not rocket science, this is simple DB concepts 101.

1. You've probably imported a load of records into RESULT after disabling the FK constraint FK_COMP.
2. The records have a COMPONENT_KEY column which has a FK link to table.column = COMPONENT.COMPONENT_KEY
3. Those values don't exist in COMPONENT.COMPONENT_KEY
4. Oracle cannot re-enable the FK constraint, because the FK is violated.

To fix, either delete those records from RESULT or insert the missing records for COMPONENT.COMPONENT_KEY

mathguy

In the interest of fairness, let me comment on your choice of "accepted answer". The very first answer you received was from BEDE. He explained how to go about finding out exactly which rows in the "child table" caused the violation, which is a step further than the answer from Paulzip. And the other answer you accepted, from Solomon Yakobson, only told you to enable NOVALIDATE. BEDE suggested exactly the same thing several replies before Solomon's answer. So, why is BEDE's answer not one of the "accepted answers"?

mathguy

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

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