Alter table can do that:
SQL> create table t (x int, constraint t_pk primary key (x) enable
novalidate);
Table created.
SQL> select constraint_name, status, validated from user_constraints where
constraint_name='T_PK';
CONSTRAINT_NAME
--------------------------------------------------------------------------------
STATUS VALIDATED
-------- -------------
T_PK
ENABLED NOT VALIDATED
SQL> alter table t modify constraint t_pk enable validate;
Table altered.
SQL> select constraint_name, status, validated from user_constraints where
constraint_name='T_PK';
CONSTRAINT_NAME
--------------------------------------------------------------------------------
STATUS VALIDATED
-------- -------------
T_PK
ENABLED VALIDATED
If there are any rows violating the constraint, the alter table will fail.
Stefan
On Fri, Jan 8, 2016 at 1:37 AM, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi Rich
The table was bulk-reloaded a year ago and the FKs were enabled using
novalidate option during a maintenance window to speed up the process but
after enable novalidate the state of VALIDATED stayed as "NOT VALIDATED". I
simply wonder if there is any DDL command to change it to VALIDATED.
Thanks
On Thu, Jan 7, 2016 at 4:22 PM, Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
wrote:
On 2016/01/07 06:56, Ls Cheng wrote:
I have some tables whose FK in dba_constraints.validated appears as "NOT
VALIDATED". I guess it's because it was once enabled using novalidate
clause.
Is it possible to "change" this to VALIDATED without validating the
constraints?
I'm struggling as to why anyone would want to do this (outside of
academic curiosity) or why any DB engine would ever allow marking an FK as
validated when it hasn't been.
It seems that one could trace a validation to generate the SQL to hack
the dictionary into doing this, but I hopefully don't need to explain why
that's a terrible idea.
Just my $.02,
Rich
