Rely constraint validated mess

Not so happy with the html support pages. Could not create a SR. Had to go back to flash pages.

Today's issue is about a constraint that is in validated state. Oracle documentation says that if you use rely constraints, you should know what you are doing. Maybe you did not know and want to go back to norely mode. Here is an example what you should not do.

SQL> create table pa (pa_id number constraint pa_pk primary key);

Table created.

SQL>
SQL> insert into pa
  2  select level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> create table ch(ch_id number constraint ch_pk primary key
  2                , pa_id not null constraint ch_pa_fk references pa
  3                )
  4  ;

Table created.

SQL>
SQL> insert into ch select level ch_id, level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk rely;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
DISABLED NOT VALIDATED RELY

SQL>
SQL> insert into ch select level+10 ch_id, level+10 pa_id from dual connect by level < 3;

2 rows created.

SQL> select * from pa;

     PA_ID
----------
         1
         2

SQL> select * from ch;

     CH_ID      PA_ID
---------- ----------
         1          1
         2          2
        11         11
        12         12

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk validate;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk norely;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable validate;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
ENABLED  VALIDATED

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

no rows selected

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

     CH_ID      PA_ID
---------- ----------
        11         11
        12         12

SQL> alter table ch modify constraint ch_pa_fk enable validate;
alter table ch modify constraint ch_pa_fk enable validate
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SYSTEM.CH_PA_FK) - parent keys not found

Going back from rely mode to norely. Go first to norely and validate after.