Oracle ForeignKey constraints with NULL Child Value

[ukyrvd]

Options:
Write Method Load
Write Mode Append
Disable Constraints TRUE

-------- Table Structure
Col1 Non-Nullable PK
Col2 Nullable FK references Col1 in the same table
..
..


There are some more referential intigrity constraints and CHECK constraints on this table .. also some indexes were defined
------------

When I tried to run a load onto this table .. its disabling all the contsraints fine, doing the load fine and enabling all the constraints fine EXCEPT when checking for constraint on Col2 as this column is nullable and Col1 doesnt have any NULL value in it.

Job finishing with following (fatal) warning:

Code: Select all

Oracle_Enterprise_52: Oracle call failed; sqlcode = -2270; message: ORA-02270: no matching unique or primary key for this column-list
Oracle_Enterprise_52: ExecuteImmediate failed for: alter table table1 enable constraint
Oracle_Enterprise_52: Unable to enable constraint "XFK_Col2_Col1" on table "table1" using alter table command: "alter table table1 enable constraintXFK_Col2_Col1".

When I execute the same alter command from SQLplus its working Okay.

Is there any way to handle this??
Thanks

[kumar_s]

The error is about the exixtance of the parent column. Check whether the give name is correct. If not try to have table.column name in the statement.

[prabu]

Options:
------------

Code: Select all

Oracle_Enterprise_52: Oracle call failed; sqlcode = -2270; message: ORA-02270: no matching unique or primary key for this column-list
Oracle_Enterprise_52: ExecuteImmediate failed for: alter table table1 enable constraint
Oracle_Enterprise_52: Unable to enable constraint "XFK_Col2_Col1" on table "table1" using alter table command: "alter table table1 enable constraintXFK_Col2_Col1".

When I execute the same alter command from SQLplus its working Okay.

Is there any way to handle this??
Thanks

looks like a straight forward error message to me. if you try to insert the following record

Code: Select all

1,  2  -> col1, col2

and 2 is not already inserted as the primary key, it fails . did u check the offending record using exceptions clause

Code: Select all

alter table <table_name> enable constraint  <Foriegn_key_constraint> exceptions into <exception_table>

[/code]

[ukyrvd]

The error is about the exixtance of the parent column. Check whether the give name is correct. If not try to have table.column name in the statement.

thanks Kumar .. BUT as I have said same alter command worked PERFECT when I ran it from SQL promt (sqlplus) ..

I will try to alter the constraint definition so as to include tablename and let you know the results.

[ukyrvd]

did u check the offending record using exceptions clause

Code: Select all

alter table <table_name> enable constraint  <Foriegn_key_constraint> exceptions into <exception_table>

[/code]

straight run or ALTER command @sqlplus prompt with out the exception clause is not at all complaining .. but only when I do it from DS its complaining .. Let me try with exception Option in DS

thanks

[ukyrvd]

I have solved this problem.

It has to do with the order in which Constraints are enabled .. We have given name to Primary Key (on Col1 in Eg) which comes later, in alphabatical order, than the reference_key name in question. So DS tried to enable FK but by that time the PK is not yet enabled. If I rename FK so that it comes after PK name, everything is working just fine.

Kumar, having the tableName.columnName in constraint definition didnt help the cause.
Prabhu, DS pushed all the records into exception table.

Thanks guys for suggestings!!

[prabu]

I have solved this problem.

It has to do with the order in which Constraints are enabled .. We have given name to Primary Key (on Col1 in Eg) which comes later, in alphabatical order, than the reference_key name in question. So DS tried to enable FK but by that time the PK is not yet enabled. If I rename FK so that it comes after PK name, everything is working just fine.

Kumar, having the tableName.columnName in constraint definition didnt help the cause.
Prabhu, DS pushed all the records into exception table.

Thanks guys for suggestings!!

changing the name of the constraint may not be the permanent solution.

please check for

Code: Select all

deferrable initially deferred

constraint.

example:-

Code: Select all

 alter table <PARENT_TABLE> add constraint <constraint_name>  PRIMARY KEY(<col_name>)  deferrable initially deferred

regards,
Prabu

[ukyrvd]

Thats one of the things I have actually looked at already .. It did not work in this case .. as I have mentioned DS tried to enable this FK first and determined that corresponding PK is not yet enabled so threw a warning and continued with rest of the constraints..

I think, Defering works when we are loading data in sequential mode (i mean SQL inserts) and instead of checking for constraints as soon as a row is inserted it will wait for commit point for checking .. now in this case we are forcefully asking that constraint to be checked!!