Cannot Validate, with Novalidate Option

Cannot validate, with novalidate option

You cannot have non-unique values with a unique index. But you can have non-unique values with a unique constraint that is enforced by a non-unique index. Even if you initially created a non-unique index, the drop index and enable syntax will try to recreate a unique index unless you provide more details in the using index section.

For example:

SQL> create table my_table(my_column number,
  2     constraint my_constraint unique (my_column));

Table created.

SQL> alter table my_table disable constraint my_constraint drop index;

Table altered.

SQL> insert into my_table select 1 from dual union all select 1 from dual;

2 rows created.

SQL> alter table my_table enable novalidate constraint my_constraint;
alter table my_table enable novalidate constraint my_constraint
*
ERROR at line 1:
ORA-02299: cannot validate (USER.MY_CONSTRAINT) - duplicate keys found

SQL> alter table my_table enable novalidate constraint my_constraint
  2     using index (create index my_index on my_table(my_column));

Table altered.

SQL> --The constraint is enforced, even though other rows violate it.
SQL> insert into my_table values(1);
insert into my_table values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (USER.MY_CONSTRAINT) violated

Why Would someone Enable Constraint with NoValidate later Validate in Oracle

Oracle recommends this order in the documentation

Efficient Use of Integrity Constraints: A Procedure
Using integrity
constraint states in the following order can ensure the best benefits:

• Disable state.
• Perform the operation (load, export, import).
• Enable novalidate state.
• Enable state.
  
  

Some benefits of using constraints in
this order are:

• No locks are held.
• All constraints can go to enable state concurrently.
• Constraint enabling is done in parallel.
• Concurrent activity on table is permitted.

novalidate with error ora-02299

If I get you correctly, you expect Oracle to ignore old duplicate values and allow new values only when they satisfy the constraint. The error is returned because when you add a UNIQUE constraint, Oracle creates unique index on the column to check the values, but your table already have duplicate values, so it fails. I would create the non-unique index first, then add the constraint so that it uses your existing non-unique index instead of automatically creating the unique index which would fail:

create index arc_ix on arc (name);

alter table arc add constraint arc_uq unique (name) enable novalidate;

novalidate attribute to specific field or to entire page

Well, novalidate property is specific to form elements. So no, you cannot set novalidate automagically to all forms in your page. But you can use a small piece of javascript code to do that for you.

var forms = document.querySelectorAll('form');

for (var i = 0; i < forms.length; i++) {
    forms[i].setAttribute('novalidate', true);
}

Take a look at this example that I made.

validation of constraints in oracle

Here's an example of the options you have

SQL> create table t1 as select distinct owner from dba_objects;

Table created.

SQL> alter table t1 add primary key ( owner );

Table altered.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL>
SQL> update t2 set owner = 'BAD_DATA' where rownum <= 10;

10 rows updated.

SQL> commit;

Commit complete.

So we want to link OWNER between T1 and T2, and T2 has some bad data. Adding a constraint gives an error

SQL>
SQL> alter table t2 add constraint fk foreign key ( owner ) references t1 ( owner );
alter table t2 add constraint fk foreign key ( owner ) references t1 ( owner )
                              *
ERROR at line 1:
ORA-02298: cannot validate (MCDONAC.FK) - parent keys not found

So the first thing I'll do is enable it in NOVALIDATE mode which means no more bad data can come in

SQL>
SQL>
SQL>
SQL> alter table t2 add constraint fk foreign key ( owner ) references t1 ( owner ) enable novalidate;

Table altered.

Now once you've done that, you can then attempt to validate the constraint. (Obviously we know this will fail, but in the general case, you would enable novalidate, and then try enable validate. That means less locking on your system)

SQL>
SQL> alter table t2 modify constraint fk enable validate;
alter table t2 modify constraint fk enable validate
                                 *
ERROR at line 1:
ORA-02298: cannot validate (MCDONAC.FK) - parent keys not found

If we want to see the actual bad data, then we can create a table called EXCEPTIONS with a predelivered script in $ORACLE_HOME/rdbms/admin.

SQL> @?/rdbms/admin/utlexcpt.sql

Table created.

Now we can extend the validate command to capture the rows. We still get the error

SQL>
SQL> alter table t2 modify constraint fk enable validate exceptions into exceptions;
alter table t2 modify constraint fk enable validate exceptions into exceptions
                                 *
ERROR at line 1:
ORA-02298: cannot validate (MCDONAC.FK) - parent keys not found

But now the bad rows have been captured into a table

SQL>
SQL> select * from exceptions;

ROW_ID             OWNER                TABLE_NAME CONSTRAINT
------------------ -------------------- ---------- --------------------
AAAySAAAHAAABhjAAA MCDONAC              T2         FK
AAAySAAAHAAABhjAAB MCDONAC              T2         FK
AAAySAAAHAAABhjAAC MCDONAC              T2         FK
AAAySAAAHAAABhjAAD MCDONAC              T2         FK
AAAySAAAHAAABhjAAE MCDONAC              T2         FK
AAAySAAAHAAABhjAAF MCDONAC              T2         FK
AAAySAAAHAAABhjAAG MCDONAC              T2         FK
AAAySAAAHAAABhjAAH MCDONAC              T2         FK
AAAySAAAHAAABhjAAI MCDONAC              T2         FK
AAAySAAAHAAABhjAAJ MCDONAC              T2         FK

10 rows selected.

If you want to run a simple query to check what rows are bad, an outer join where you want to get the rows that did NOT manage to join will do the trick as well.

SQL>
SQL> select t2.owner, t2.rowid
  2  from t1,
  3       t2
  4  where t2.owner = t1.owner(+)
  5  and t1.owner is null;

OWNER                ROWID
-------------------- ------------------
BAD_DATA             AAAySAAAHAAABhjAAA
BAD_DATA             AAAySAAAHAAABhjAAB
BAD_DATA             AAAySAAAHAAABhjAAC
BAD_DATA             AAAySAAAHAAABhjAAD
BAD_DATA             AAAySAAAHAAABhjAAE
BAD_DATA             AAAySAAAHAAABhjAAF
BAD_DATA             AAAySAAAHAAABhjAAG
BAD_DATA             AAAySAAAHAAABhjAAH
BAD_DATA             AAAySAAAHAAABhjAAI
BAD_DATA             AAAySAAAHAAABhjAAJ

10 rows selected.

SQL>
SQL>
SQL>

---

Related Topics