Обсуждение: Check constraint and at least two rows
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?
Thanks,
Dane
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?
"""
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.
On Tue, Nov 3, 2015 at 7:09 PM, David G. Johnston <david.g.johnston@gmail.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?"""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.
I hear ya but ... what I'm working on is the migration of an existing application from MySQL to PostgreSQL. And one of the things I've been playing w/ recently is the mysql_fdw to do the migration of the data. I've already encountered cases where implicit business rules have been violated because there is no generalized constraint mechanism in MySQL (e.g., NOT NULL doesn't count!) to make them explicit. So since PostgreSQL has a generalized constraint mechanism (because NOT NULL doesn't get you very far) I'd like to take advantage of it and eliminate the possibility of developers screwing up my data model and data. Because I have the luxury of knowing what ALL the requirements are I don't have to play the "I need to be flexible" game. The game I want to play is, "ha you can't introduced logical inconsistencies into my data model!"
Dane
On 11/03/2015 04:23 PM, Dane Foster wrote: > On Tue, Nov 3, 2015 at 7:09 PM, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > On Tue, Nov 3, 2015 at 4:55 PM, Dane Foster <studdugie@gmail.com > <mailto:studdugie@gmail.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. > > > I hear ya but ... what I'm working on is the migration of an existing > application from MySQL to PostgreSQL. And one of the things I've been > playing w/ recently is the mysql_fdw to do the migration of the data. > I've already encountered cases where implicit business rules have been > violated because there is no generalized constraint mechanism in MySQL > (e.g., NOT NULL doesn't count!) to make them explicit. So since > PostgreSQL has a generalized constraint mechanism (because NOT NULL > doesn't get you very far) I'd like to take advantage of it and eliminate > the possibility of developers screwing up my data model and data. > Because I have the luxury of knowing what ALL the requirements are I > don't have to play the "I need to be flexible" game. The game I want to > play is, "ha you can't introduced logical inconsistencies into my data > model!" Seems to me you are looking at a middleware layer, either outside the database or inside that has the logic encoded into functions. The FOR EACH STATEMENT would work only if you forced the users to insert at least two rows at a time as it would still fire on a single row entry and trip the constraint if that was the only row in the child that matched a row in the parent. > > Dane > -- Adrian Klaver adrian.klaver@aklaver.com