Is it possible to have a SQL constraint (I am on mysql at the moment)
Background: Table has a parent_id column that allow a row in the table
reference another row in the table. The table list bank transactions,
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
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
can’t implement such a constraint?