CODEWITHSUNDEEP
databaseoracle-database
kishore
kishore

Reputation: 167

referential integrity constraint is automatically disabling in oracle

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..........

Upvotes: 0

Views: 1850

Answers (3)

dpbradley
dpbradley

Reputation: 11915

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.

Upvotes: 1

Jim Hudson
Jim Hudson

Reputation: 8069

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.

Upvotes: 1

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

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.

Upvotes: 0

Related Questions