Re: dba_constraints.validated

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Fri, 8 Jan 2016 01:45:32 +0700
Message-ID: <CAP50yQ_DQFvwsPQKjNvRO38tj0fmjRZiJuXDTfdRAEmmTeMaFg_at_mail.gmail.com>

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_at_gmail.com> 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_at_society.servebeer.com>
> 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
>>
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 07 2016 - 19:45:32 CET