Re: multi-row check constraints?

On Mar 22, 8:09 pm, p(dot)(dot)(dot)(at)j-davis(dot)com (Jeff Davis) wrote:
> On Tue, 2007-03-20 at 13:21 -0700, Angva wrote:
>
> Your Oracle solution is interesting, and can indeed be implemented in
> PostgreSQL in exactly the same way. Look at materialized views here:
>
> http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html
>
> Another way to do it without using an entire materialized view is to
> obtain a row level lock on the ID using SELECT ... WHERE id=123 FOR
> UPDATE. To do this you need to have a table that contains all the IDs
> and where id has a unique index to prevent race conditions when adding
> new IDs.
>
> What are you trying to do exactly? Why does the table have no primary
> key?

Thanks for that info. The materialized view functions look a bit
unwieldy for this one purpose...Your other idea might be simpler to
implement.

What I'm trying to accomplish...I'm dealing with financial accounts,
each of which has a purchase limit of $x, and one or more sub-
accounts. Each sub-account is allocated a percentage of this limit. So
account A could have sub-accounts 1, 2 and 3, with 50%, 30% and 20%
respectively allocated. Or there could be just one sub-account with
exactly 100%.

The "real" table actually does have a primary key - I was using a
simplified example to make my point.

Thanks for your input,
Mark