Preconditions
Automatic Constraint Validation Pro will work in pages with APEX Form and Interactive Grid regions where the source type is a simple SQL Query or SQL Table (table, view, or synonym name) and where the source location is set to 'Local Database'. When the region's source type is SQL Query, the query may have only one FROM clause where the plug-in should detect the table name.
The plug-in will work best with forms on tables or views with well-defined data types and constraints.
- Number, float, date, datetime, timestamp, and interval data types should limit the precision and scale where a valid range should be enforced.
- CHECK constraints on database tables should exist for columns where the list of values or a valid range should be enforced.
- NOT NULL constraints should exist for required columns.
- PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints on database tables and views should be implemented to effortlessly enforce data integrity.
- For multi-column constraints, all involved columns have to be selected in the query and must exist in the grid or form as visible or hidden items to perform the validations.
- For each UNIQUE index, there must exist a corresponding UNIQUE constraint when a duplicate key should be detected.
- For UNIQUE constraints, set the timing property to DEFERRABLE INITIALLY DEFERRED to prevent temporary duplicate key errors while processing the DML of Interactive Grids.
- Constraints may address single or multiple columns in the constraint definitions. Single column constraints are equivalent to APEX Item/column validation rules, while multi-column constraints are equivalent to APEX row validation rules.