when am going to insert data into a child table for which i have added constraint(foreign key)
the constraint is automatically disabling.
can u please help me..........
+1
A:
The constraint can't "automatically" disable - check it's current state by issuing this SQL (as the table/constraint owner):
select status, validated from user_constraints where constraint_name = 'theNameOfYourConstraint'
It might be the case that it has been disabled prior to your operation. It will remain in that state until explicitly enabled.
[Additional]
I see from a later comment now that the disabling appears to be due to a direct path load in SQL*Loader. If you want to prevent this I think your only option is to use a conventional load and process the exceptions. Binding the file to an external table definition will allow you to use a more procedural approach since you can process the file as if it were an Oracle table.
From the Oracle docs:
Integrity constraints that depend on other rows or tables, such as referential constraints, are disabled before the direct path load and must be reenabled afterwards.
dpbradley
2009-06-15 12:45:04
i checked before inserting data..........the constraint is enabled.but when am inserting data into that table that constraint is automatically going into disable state.
kishore
2009-06-15 12:48:40
So I understand the sequence of events to be this: 1) you can look at the constraint with the sql above and see the status as enabled. 2) you execute an insert 3) you run the sql against user_constraints again and see the status as disabled?
dpbradley
2009-06-15 12:51:33
yes exactly.........
kishore
2009-06-15 13:09:38
actually its working when am inserting data by using normal insert statements.but the above problem is coming when am inserting data using sql loader.
kishore
2009-06-15 13:46:58
Ok, sql loader - then you must be using a direct path load - correct?
dpbradley
2009-06-15 14:01:05
A:
You'll have to provide us with some more information/code. Describe your constraint. How are you inserting records?
- insert statement
- calling a procedure
- sql loader
- etc.
Reproduce your problem:
select *
from user_constraints
where constraint_name = 'theNameOfYourConstraint';
do your insert here
select *
from user_constraints
where constraint_name = 'theNameOfYourConstraint';
and show us the output.
Robert Merkwürdigeliebe
2009-06-15 13:18:58
actually its working when am inserting data by using normal insert statements.but the above problem is coming when am inserting data using sql loader.
kishore
2009-06-15 13:47:35
That's kind of an important detail. http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10825/ldr_modes.htm#i1008078With the conventional path load method, arrays of rows are inserted with standard SQL INSERT statements—integrity constraints and insert triggers are automatically applied. But when you load data with the direct path, SQL*Loader disables some integrity constraints and all database triggers. This section discusses the implications of using direct path loads with respect to these features.
Robert Merkwürdigeliebe
2009-06-16 06:45:14
+1
A:
From the Utilties manual, relational integrity and check constraints are automatically disabled for direct path loads. Conventional path shouldn't have this problem.
There's a REENABLE clause to enable the constraints at the end of a direct path load.
Jim Hudson
2009-06-15 14:02:17