Declaring NOVALIDATE on Constraints Causes Cache Group Failure

If the Oracle database table on which you want to create the cache group declares NOVALIDATE on columns with primary key, UNIQUE or NOT NULL constraints, the creation of the cache group fails.

Note:

This does not apply to any foreign key constraints. However, TimesTen recommends that any matching foreign key is in the enabled VALIDATE state. Your workload performance may be affected when you alter a foreign key column to the enabled VALIDATE state.

TimesTen perceives a NOVALIDATE on a primary key or NOT NULL table column definition as a NULL and, therefore, not qualified as a column on which to build the cache group. Thus, all columns with the primary key, UNIQUE and NOT NULL column constraints must be enabled with the VALIDATE state when creating a cache group from the Oracle database table.

When you create a cache group from an Oracle database table with one or more of these constraints, the following errors are thrown:

5124: Autorefresh/propagate are not allowed on restricted cache group
5168: Restricted cache groups are deprecated
5120: No matching unique index with not null columns, unique key constraint
 with not null columns, or primary key constraint on table EVENTLOG, cache
 operations are restricted.

If you receive these errors, you can perform a SELECT statement to verify any existing NOVALIDATE constraints on the Oracle database table. The following SELECT statement shows all constraints on the MyTable table:

SQL> select constraint_name, constraint_type, validated, status from 
        all_constraints where table_name = 'MyTable';
 
CONSTRAINT_NAME                C VALIDATED     STATUS
------------------------------ - ------------- --------
REFID_CONSTRAINT               C VALIDATED     ENABLED
PKEY_CONSTRAINT                P NOT VALIDATED DISABLED

If the table column that is to be the primary key for the cache table is enabled as NOVALIDATE, perform the following steps to enable the column with the VALIDATE state:

1. Enable the NOVALIDATE state for the primary key column.

   SQL> alter table MyTable modify constraint PKEY_CONSTRAINT 
              enable novalidate;
   Table altered.
    
   SQL> select constraint_name, constraint_type, validated, status 
             from all_constraints where table_name = 'MyTable';
    
   CONSTRAINT_NAME                C VALIDATED     STATUS
   ------------------------------ - ------------- --------
   REFID_CONSTRAINT               C VALIDATED     ENABLED
   PKEY_CONSTRAINT                P NOT VALIDATED ENABLED
   

2. Enable the VALIDATE state for the primary key column.

   SQL> alter table MyTable modify constraint PKEY_CONSTRAINT validate;
   Table altered.
    
   SQL> select constraint_name, constraint_type, validated, status 
             from all_constraints where table_name = 'MyTable';
    
   CONSTRAINT_NAME                C VALIDATED     STATUS
   ------------------------------ - ------------- --------
   REFID_CONSTRAINT               C VALIDATED     ENABLED
   PKEY_CONSTRAINT                P VALIDATED     ENABLED