Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: