Page 1 of 1
Oracle ForeignKey constraints with NULL Child Value
Posted: Fri Sep 08, 2006 3:44 pm
by 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
Posted: Sat Sep 09, 2006 12:27 am
by 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.
Re: Oracle ForeignKey constraints with NULL Child Value
Posted: Sat Sep 09, 2006 7:42 am
by prabu
ukyrvd wrote: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
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]
Posted: Sat Sep 09, 2006 11:00 am
by ukyrvd
kumar_s wrote: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.
Re: Oracle ForeignKey constraints with NULL Child Value
Posted: Sat Sep 09, 2006 11:04 am
by ukyrvd
prabu wrote: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
Posted: Mon Sep 11, 2006 11:48 am
by 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!!
Posted: Mon Sep 11, 2006 12:41 pm
by prabu
ukyrvd wrote: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
constraint.
example:-
Code: Select all
alter table <PARENT_TABLE> add constraint <constraint_name> PRIMARY KEY(<col_name>) deferrable initially deferred
regards,
Prabu
Posted: Mon Sep 11, 2006 1:57 pm
by 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!!