ADF BC 11g – Creating business rules for unique keys on actual database unique keys (rather than primary keys)

Phew, that's the best title I could think of for this post. Not the most inspiring I'd agree. Anyway, let's continue.

Users of JDeveloper 11g ADF Business Components will be familiar with the ability to create Declarative Validation Rules on an EO's underlying table's primary key, somewhat confusingly (as we will see) referred to as a "UniqueKey" validator, though it's based on a primary key. The main reason to create such a rule is to avoid the particularly unfriendly database constraint error that is thrown, to replace it with a more friendly error message defined in ADF BC.

This "UniqueKey" rule can easily be extended to actual schema unique keys through a little trick (as separate to a primary key) as we'll see in this post.

When an EO is created in your Model project based, the IDE takes the opportunity to capture all the constraints of the underlying table (or you can resync these at a later date by right clicking the EO in the App Navigator and choosing the Synchronize with Database option). To create a "UniqueKey" validator based on the primary key of the table you:

a) Open the EO editor
b) Select the Business Rules node
c) Then Entity Validators followed by the plus button

In the resulting Add Validation Rule dialog, selecting the Rule Type of "UniqueKey" gives you the option to pick the relating EO's primary key based on the same constraint from the database:


However you'll notice what it doesn't display is any actual unique keys (such as say a database constraint employees_uk that enforces the first_name and last_name must be unique within the employees table).

One solution to this is to define Alternate Keys in the EO via the EO editor's General page's same named option:


Once created the Alternate Key will then appear as a "UniqueKey" option in the Add Validation Rule dialog.

However there is an easier way.

If you select an EO in the Application Navigator, then open the Structure Window option, you get a list of parts of the EO including Constraints, which will include all the database constraints for the particular EO. As can be seen in the following picture, this includes not only the primary key, but also unique keys:

If you select the unique key constraint and then open the Property Inspector you'll see:


...including an option to create the unique key constraint as an Alternate Key (the default being false). Setting this to true means that when you create the validation rule for a "UniqueKey", you'll see the unique key constraint listed as well as the primary key:


This little trick saves us having to manually create Alternate Keys based on the columns in our unique keys, but rather reuse the unique key constraint definition to define the Alternate Keys for us.