Q

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?

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?
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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close