By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.