Check constraint where all records with the same effective date add up to one (1 Viewer)

Hi Dev Masters,

I had to call uncle on this one!

I am trying to build a check constraint on a table that tracks shared resources using an EAV model.

The shared resource percentage does not care how many components are included in the whole but does care that the components add up to 100 percent for a given effective date. I was trying use a natural key for the PK (see code below) but I ran into a scenario where on a specific date for the same entity, entity type, and attribute id I need to record factors that add up to 1 (or 100% ) depending on how you look at it.

Thanks for any help!

For example.

Entity = Food Plate
Entity type = Dinner
Effdate = 1/1/2016
entAttVal_int (FK to food component table) = Carrot
entAttVal_dbl (% of plate utilized by carrot) = 25%




Thanks a ton if you have any ideas! The T-SQL code I tried to use to solve the problem is shown below.

This looks like some kind of meal planner app?

The problem I see is that your constraint involves multiple records to get to that 100% total that you seek, and a table constraint won't do that. Table-based constraints will only tell you whether INDIVIDUAL numbers are >0, between 0 and something else, thinks like that. Constraints are normally per-record and are hard as hell to implement as some kind of cross-record constraint. Allen Browne suggested once that a method existed but he thought it was extremely difficult. If Allen Browne says it is difficult, I ain't touching it with a 10-foot pole.

The solution is to think outside of one box (and into another box). The forms that you could build/use for data entry CAN do things like DSum() for a particular field based on some common selection criteria, like all records for the same plate ID. The forms can then warn you of constraint violations and can even list (via sub-form, perhaps) the records to show you the amount of overfill / underfill.

Unfortunately, in New Orleans we NEVER have under filled plates and ALL TOO OFTEN have overfilled ones. This is a city that loves to eat so I have no experience with a balanced meal that DOESN'T overflow the plate. OK, maybe I'm joking just a little?

But seriously, a table constraint is not the answer because those are record-by-record constraints. A form constraint would do it. Or you can run a query that generates the sums and then write a query of THAT query to show you sums (and IDs) where the total isn't 100%.

There is a third issue, and this one is trickier. Using a FLOAT is fine for percentages until you realize that if you store them as fractions, even things that SHOULD add up to 100% won't do so unless you do some kind of rounding. On the other hand, if you just said you would NEVER allow fractional percentages (i.e. NEVER use 33 1/3 % represented as 0.3333...) then you could use integer percentages that will always add up correctly.

This is because fractions based on decimal percentage equivalents RARELY come out even. The number 1/10 in decimal is 0.1, right? But in binary, 1/10 is 0.000110011001100110011..... ad infinitum, but truncated to less than an infinite number of bits since it has to fit into a FLOAT's mantissa. So you get rounding errors for things based on truncated decimal fractions. DOUBLE doesn't fix it because the truncation still happens there, too. Just more bits in the mantissa, but still a limited number of bits that still get truncated.

As it happens, if you use a FLOAT for numbers that if printed would appear as 35.0 meaning 35%, that works - but using an INTEGER or LONG to say 35 (and you just understand it to be a percentage) works just as well and has no chance of math errors.

This looks like some kind of meal planner app?

Click to expand...

It is an app to help allocate costs for equipment that is shared between cost centers. I think I was hungry when I wrote the post. Food is magical when describing database design issues since we all can relate.

Constraints are normally per-record

Click to expand...

I was working with another colleague and they said the same thing. You are on point DOC

Allen Browne suggested once that a method existed but he thought it was extremely difficult

Click to expand...

Where could a masochist learn more about this technique?

The solution is to think outside of one box (and into another box)

Click to expand...

Perhaps you are right about doing this in the application layer. I try to build the business logic on the server if possible but the only other option I have considered is an insert/update/delete trigger that flags the group as whole or incomplete.

There is a third issue, and this one is trickier. Using a FLOAT is fine for percentages until you realize that if you store them as fractions, even things that SHOULD add up to 100% won't do so unless you do some kind of rounding

Click to expand...

Integers sound like a good path forward.

Thanks Doc Man! You make this forum great!

Where could a masochist learn more about this technique?

Click to expand...

Unfortunately, I can't put my finger on the reference at the moment, though I know it was made from this forum. I know it was a note in passing and that Allen Browne had posted it on his web site as a link to another article.

Here are a couple of constraint articles you might find helpful.

https://msdn.microsoft.com/en-us/library/office/ff836971.aspx

https://www.sqlservercentral.com/Forums/Topic1189678-149-1.aspx

https://stackoverflow.com/questions/2588072/how-do-i-create-a-multiple-table-check-constraint

https://www.google.com/url?sa=t&rct...d/7999/8053/&usg=AOvVaw2VcC1Ug1khcLzTQugogMb- (Opens a PDF)