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

rubyonrails-talk
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 Hauptmann 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