Is it possible to have a SQL constraint (I am on mysql at the moment) for the following?
Background: Table has a parent_id column that allow a row in the table to reference another row in the table. The table list bank transactions, but allow the ability to say row A is actually made up of two separate items (which you enter manually) in row B & C. For example:
Row 1 - $100 (bank transaction)
Row 2 - $20 (item X), parent_id=1
Row 3 - $80 (item Y), parent_id=1
Q - Can I create a constraint that requires, for a successful save, that SUM(parent) = SUM(children).
That is this would make sure the table data is always consistent. It would not be possible to allocate Row 2 as child and forget to add Row 3 as remaining child, as they wouldn’t add up.
Is this possible? Or is there a way to protect against this somehow? Is there anything in Rails that could help at the application layer if mysql can’t implement such a constraint?