Home » SQL & PL/SQL » SQL & PL/SQL » Enabling the Constraint With Enable No Validate
Enabling the Constraint With Enable No Validate [message #595021] Thu, 05 September 2013 02:31 Go to next message
saipradyumn
Messages: 182
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

Is there any way to enable the constraint with out validating for the existing information.

As We can accomplish the same functionality when we are adding the constraint for the first time.

Ex:
  alter table scott.emp add constraint fk_deptno foreign key(deptno) references scott.dept(deptno) enable novalidate


The above SQL statement will enable the constraint with out validating for the existing information.
So there is possibility of data will be there only in the child table but not in the parent table for existing information.

But in the future it will not allow to do so,if the constraint is in ENABLE staus.
So i am just trying to disable the constraint to insert only into child table & trying to enable it with NOVALIDATE option .

But the following exception is coming .

SQL> alter table scott.emp  ENABLE  constraint fk_deptno enable NOVALIDATE;
 
alter table scott.emp  ENABLE  constraint fk_deptno enable NOVALIDATE
 
ORA-00905: missing keyword
 
SQL> alter table scott.emp  ENABLE  constraint fk_deptno  NOVALIDATE;
 
alter table scott.emp  ENABLE  constraint fk_deptno  NOVALIDATE
 
ORA-00933: SQL command not properly ended


Instead of dropping & recreating the same constraint , is there any alternate way to do this ?

Thanks
SaiPradyumn

Re: Enabling the Constraint With Enable No Validate [message #595026 is a reply to message #595021] Thu, 05 September 2013 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try:
alter table scott.emp  ENABLE NOVALIDATE constraint fk_deptno;  

Regards
Michel
Re: Enabling the Constraint With Enable No Validate [message #595028 is a reply to message #595026] Thu, 05 September 2013 02:59 Go to previous messageGo to next message
saipradyumn
Messages: 182
Registered: October 2011
Location: Hyderabad
Senior Member


Thanks Michel
Re: Enabling the Constraint With Enable No Validate [message #595119 is a reply to message #595021] Fri, 06 September 2013 22:21 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Be careful in maintaining the constraint in a NOVALIDATE state. This could affect the optimizer.
Previous Topic: select max date and Pk
Next Topic: Breaking up an expression to evaluate
Goto Forum:
  


Current Time: Tue Jul 29 07:46:58 CDT 2014

Total time taken to generate the page: 0.94501 seconds