Re: Check constraint and at least two rows

On Tue, Nov 3, 2015 at 4:55 PM, Dane Foster <studdugie(at)gmail(dot)com> wrote:

> Hello,
>
> I have a design/modelling puzzle/problem. I'm trying to model a series of
> events. So I have two tables w/ a parent child relationship. The child
> table has the rule/constraint/etc that for every row in the parent table
> there must be at least 2 rows in the child because a series must have at
> least 2 events to be a series.
>
> Now the SQL for the constraint is straight-forward and easy to write. What
> I haven't figure out yet is where to put it because a straight forward
> table constraint won't work because it's checked on every INSERT which
> means it will be tripped on the first row inserted. A trigger doesn't seem
> to fit the bill either.
>
> Ideas?
>
>
​http://www.postgresql.org/docs/devel/static/sql-createtrigger.html
​

​"""​
In contrast, a trigger that is marked FOR EACH STATEMENT only executes once
for any given operation, regardless of how many rows it modifies (in
particular, an operation that modifies zero rows will still result in the
execution of any applicable FOR EACH STATEMENT triggers
​.
"""

​That said while the "perfect" model may indeed conform to your definition
as a practical matter what harm would there be in allowing zero or one
child records for a given parent? Usually problems stem from designing a
"zero-or-one" setup and then realizing that you actually have a "as many as
you want" situation.​ Allowing a "as many as you want" setup to choose
zero or one is significantly less problematic though you do need to be more
aware of the need for LEFT JOINs.

David J.

​