Can I have a constraint like this.....?

Rails
1

Hi,

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?

Thanks

2

Greg H. wrote:

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?

Thanks

I’m not sure about MySQL, but you could probably do something with a
custom validation in Rails. Google for

Rails “custom validations”

and you should find plenty to get you going.

Peace,
Phillip