Friday, March 9, 2012

Defining Unique Key Constraints for Non-Primary Key Attributes in ADF-BC

Greetings,

ADF BC (Business Components) allows you to write code declaratively instead of writing spaghetti code in Java.

One of the feature is Alternate key constraint which is different from Primary and Unique key constraints available in database. Usually Primary key values are coming from database sequence so you don't have to worry about their uniqueness but if you like to validate uniqueness on other columns e.g. email address,Region or Country Name that's where Alternate key comes into action.

Following are some properties of alternate keys:

  • You can check Uniqueness at record level e.g. Department Name must be unique 
  • You can have many alternate keys as compare to Primary keys
  • You can look for a row in entity object using findByKey() method
  • If the key is found in entity objects ADF will throw TooManyObjectsException


Lets look into example (Download the complete example)

Use case:
Database schema: HR @ Oracle XE

Column Country name must be unique in Countries table. We have primary key CountryId in the table as shown in the slide




Solution:

  1. Create an Alternate key constraint on Entity object.
  2. Add Unique Key validation on Entity Object using alternate Key

Creating Alternate Key:

Right click the Entity Object and Choose "New Entity Constraint"



Enter the name of the constraint, Enter the Attribute where you want to add constraint in our case select Country Name





You can verify the Alternate key constraint Under general tab of entity object properties




Creating Unique Validation:

Go to Business Rules tab in Entity Object properties and Click Create New Validation by selecting "Entity Validators" as shown in the slide



Select "Unique Key" from Rule Type list. Select the Alternate key constraint from Keys


Go to Failure tab and Enter custom error message



Run the Application Module, Create new Record and Enter Unique country name like Brazil, You would see the similar error message.


Also on JSF page you would see error message on Commit



Happy JDeveloping,
Zeeshan Baig







10 comments:

  1. Very Interesting !! It really helped me a lot!!

    ReplyDelete
  2. Thanks Baig but there is a problem,
    If you make country name not mandatory
    and inserted empty country name and press commit.
    if you tried to update country name to an existing name for example France, the unique constraint will not fire.
    I don't know if this is ADF bug or what.

    ReplyDelete
  3. Hi,
    I am having a use case in which I need to call alternate key validation during inserting a row into VO through a button call. In this scenario how we can call the EO validator?. EO validator works fine at the time of commit but my use case is not to commit...Please advise.

    Thanks,
    Alok

    ReplyDelete
    Replies
    1. You can call ValidateEntity() method of Entity. Check this link for more info http://docs.oracle.com/cd/B14099_19/web.1012/b14022/oracle/jbo/server/EntityImpl.html#validateEntity__

      ZB

      Delete
  4. Hi Zeeshan,

    When u select an alternate key in property inspector in other tab u get an option of Primary Key as true or false.

    What exactly does this Primary Key reflects, and how does it effects the working.

    ReplyDelete
  5. Hi Zeeshan,

    I have a doubt related the format of the message displayed.
    Your example in jsf page display "Please enter unique country name" and in the Application Module it displays "JBO-com.baigzeeshan.alternateKey.model.Countries_Rule_0: Please entery unqiue Country Name".
    My doubt is because I have done something similar but on JSF page displays exactly the same error as in the Application Module, I mean, it also displays the JBO part. Is there a way to get rid of the JBO part for the JSF page?

    ReplyDelete
    Replies
    1. Hi,

      You can customized the database error messages check this http://docs.oracle.com/cd/E24382_01/web.1112/e16182/validation_model.htm#BEIJJDEC

      Zeeshan

      Delete
  6. Thanks for sharing this Mr. Baig. Very useful one!

    ReplyDelete
  7. Hi,

    This is interesting, but what if you enter 'brazil', not 'Brazil'? Is there a way to make the AltKey work for such cases?

    Regards,
    Michal

    ReplyDelete
    Replies
    1. By default it is case sensitive but you can change the behavior by extending the framework. I will find some links for you later.

      Zeeshan

      Delete