Sum of all percentanges for a customer must be 1

My table is as follows: Preference (login:str(10), symbol:str(20), percentage:real) : (PK: (login,symbol)), where percentage is a fraction of one unit for the corresponding mutual fund designated by symbol. The sum of all percentages (fractions of 1) for a customer (login) must be exactly 1 corresponding to 100%. My question is how to implement this constraint?

    Requires Free Membership to View

    Login

You've probably discovered that the normal constraints like NOT NULL, UNIQUE and CHECK cannot be used to implement your business rule. So complex constraints like this are normally implemented in a trigger on the table. However, the trigger will check the rows of data, row by row. Since the first row will most likely not sum up to 100%, the trigger will raise an error. Because of this specific business rule, this is probably one of the rare cases where I would code this logic into the application. Before the application sends the rows of data to the database, it should verify the sum of the percentages equals 100%. If not, then raise an error to the end user.

This was first published in October 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top