Обсуждение: *Proper* solution for 1..* relationship?

It hit me today that a 1..* relationship can't be implemented just by a
single foreign key constraint. I must have been sleeping very deeply not
to notice this.

E.g. there is a table "list" and another table "list_item" and the
relationship can be described as "every list has at least one
list_item" (and every list_item can only be part of one list, but
this is trivial).

A "proper" solution would require:

1. A foreign key pointing from each list_item to its list

2. Another foreign key pointing from each list to one of its list_item.
But this must be a list_item that itself points to the same list, so
just a simple foreign key constraint doesn't do it.

3. When a list has more than one list_item, and you want to delete the
list_item that its list points to, you have to "re-point" the foreign
key constraint on the list first. Triggers, stored procedures...?

(4. Anything else that I've not seen?)

Is there a "straight" (and tested) solution for this in PostgreSQL, that
someone has already implemented and that can be re-used?

TIA,

Sincerely,

Wolfgang

Wolfgang Keller-2 wrote
> It hit me today that a 1..* relationship can't be implemented just by a
> single foreign key constraint. I must have been sleeping very deeply not
> to notice this.
>
> E.g. there is a table "list" and another table "list_item" and the
> relationship can be described as "every list has at least one
> list_item" (and every list_item can only be part of one list, but
> this is trivial).

Change the cardinality to "0..*" ?

If you want to enforce a view of the data the ensures "1..*" (for list) then
create a view of "list" where only those entries with values in "list_item"
are shown.

Alternately you might simply add a counter column to the list table and use
triggers to increment/decrement the "item_count".

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Proper-solution-for-1-relationship-tp5753384p5753392.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

> If you want to enforce a 1 to N relationship, You can use a CONSTRAINT
> TRIGGER on the List table to ensure that there is at least one list
> item - if there isn't then raise an exception 'No List Items!!!'.
> You'd want to define the constraint as INITIALLY DEFERRED.
> http://www.postgresql.org/docs/9.2/interactive/sql-createtrigger.html
> http://www.postgresql.org/docs/9.2/interactive/sql-set-constraints.html
>
> A constraint trigger is the closest feature to an ASSERTION statement
> defined by the SQL standard.

Thanks, but the main issue for me is the precise definition of the
constraint to apply.

This issue should be as old as relational databases per se. I just can't
imagine no one has implemented a well-proven "standard" solution yet.

Sincerely,

Wolfgang

Wolfgang Keller <feliphil@gmx.net> wrote:

> It hit me today that a 1..* relationship can't be implemented just by a
> single foreign key constraint. I must have been sleeping very deeply not
> to notice this.
>
> E.g. there is a table "list" and another table "list_item"
> and the
> relationship can be described as "every list has at least one
> list_item" (and every list_item can only be part of one list, but
> this is trivial).
>
> A "proper" solution would require:
>
> 1. A foreign key pointing from each list_item to its list
>
> 2. Another foreign key pointing from each list to one of its list_item.
> But this must be a list_item that itself points to the same list, so
> just a simple foreign key constraint doesn't do it.
>
> 3. When a list has more than one list_item, and you want to delete the
> list_item that its list points to, you have to "re-point" the foreign
> key constraint on the list first. Triggers, stored procedures...?
>
> (4. Anything else that I've not seen?)
>
> Is there a "straight" (and tested) solution for this in PostgreSQL,
> that
> someone has already implemented and that can be re-used?

The most straightforward way I know to enforce this is to check
that at least one child exists in a DEFERRED trigger on the the
parent.  You still need to worry about concurrency issues.  One way
to do that is to use only SERIALIZABLE transactions.  There are
other ways, though they take more to describe and to implement.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> The most straightforward way I know to enforce this is to check
> that at least one child exists in a DEFERRED trigger on the the
> parent.  You still need to worry about concurrency issues.

Imho it's absurd that I have to do this ("worry about concurrency
issues") myself, how long - more than fourty years after the invention
of relational databases?

As a non-computer scientist by education?

> One way to do that is to use only SERIALIZABLE transactions.  There
> are other ways, though they take more to describe and to implement.

What still astounds me is that, again, this (correct implementation of
1..n relationships with n>0) is an absolutely standard issue that is as
old as relational databases per se and NO ONE has implemented (and
documented and tested and...) a standard solution yet?

Gosh.

What were all those people doing all those decades.

Sincerely,

Wolfgang

On 30 April 2013 12:56, Wolfgang Keller <feliphil@gmx.net> wrote:
>> The most straightforward way I know to enforce this is to check
>> that at least one child exists in a DEFERRED trigger on the the
>> parent.  You still need to worry about concurrency issues.
>
> Imho it's absurd that I have to do this ("worry about concurrency
> issues") myself, how long - more than fourty years after the invention
> of relational databases?
>
> As a non-computer scientist by education?
>
>> One way to do that is to use only SERIALIZABLE transactions.  There
>> are other ways, though they take more to describe and to implement.
>
> What still astounds me is that, again, this (correct implementation of
> 1..n relationships with n>0) is an absolutely standard issue that is as
> old as relational databases per se and NO ONE has implemented (and
> documented and tested and...) a standard solution yet?
>
> Gosh.
>
> What were all those people doing all those decades.

And this is exactly the point where people answer for themselves the question:
"Should I get involved and help change things?".

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Wolfgang Keller-2 wrote
> What still astounds me is that, again, this (correct implementation of
> 1..n relationships with n>0) is an absolutely standard issue that is as
> old as relational databases per se and NO ONE has implemented (and
> documented and tested and...) a standard solution yet?

I would imagine most people are content using a 0..* cardinality instead of
a 1..*.  Please, someone espouse the practical benefits of enforcing that
one record exists on the child table in order for a record to be present on
the parent.


> Gosh.
>
> What were all those people doing all those decades.

Inventing the Internet and, more recently, NoSQL databases.  I guess the
problem was so difficult people just decided to get rid of cardinality
altogether.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Proper-solution-for-1-relationship-tp5753384p5753768.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On Tue, Apr 30, 2013 at 7:56 AM, Wolfgang Keller <feliphil@gmx.net> wrote:
>> The most straightforward way I know to enforce this is to check
>> that at least one child exists in a DEFERRED trigger on the the
>> parent.  You still need to worry about concurrency issues.
>
> Imho it's absurd that I have to do this ("worry about concurrency
> issues") myself, how long - more than fourty years after the invention
> of relational databases?

You're not the first one to wish for something like this, and the SQL
standard actually has the CREATE ASSERTION syntax which I believe
would be able cover your use-case. Unfortunately, almost no databases
support this feature :-(

Josh

On Tue, Apr 30, 2013 at 7:56 AM, Wolfgang Keller <feliphil@gmx.net> wrote:

The most straightforward way I know to enforce this is to check
that at least one child exists in a DEFERRED trigger on the the
parent.  You still need to worry about concurrency issues.

Imho it's absurd that I have to do this ("worry about concurrency
issues") myself, how long - more than fourty years after the invention
of relational databases?

You're not the first one to wish for something like this, and the SQL
standard actually has the CREATE ASSERTION syntax which I believe
would be able cover your use-case. Unfortunately, almost no databases
support this feature :-(

Josh

> > What still astounds me is that, again, this (correct implementation
> > of 1..n relationships with n>0) is an absolutely standard issue
> > that is as old as relational databases per se and NO ONE has
> > implemented (and documented and tested and...) a standard solution
> > yet?
>
> I would imagine most people are content using a 0..* cardinality
> instead of a 1..*.

Then they don't seem to care for reality. Which is *the* diagnostic
criterion for what psychiatrists call a "psychosis".

Cardinalities, like most aspects of data models, are imposed by
reality (ever happened to have heard of that concept?). If the data
model doesn't match reality, this will be very expensive for someone,
sooner or later.

That this "someone" who in the end has to bear the consequences is
usually not the "database administrator" or "IT manager" etc. is
obvious, unfortunately.

> Please, someone espouse the practical benefits of enforcing that one
> record exists on the child table in order for a record to be present
> on the parent.

In case of e.g. medical information systems, or in my case, maintenance
information systems, quite a few people's lives could depend on the
integrity of the data in the database. A correct data model is one
necessary prerequisite for data integrity.

Is that not enough of an incentive, to not practise homicide (murder?)
by pure lazyness/ignorance/incompetence?

Sincerely,

Wolfgang

P.S.: I would have expected people with such an obscene attitude ("why
care for data correctness") on a list for Access or similar crapware,
but not on a list for PostgreSQL.

Wolfgang Keller-2 wrote
>> Please, someone espouse the practical benefits of enforcing that one
>> record exists on the child table in order for a record to be present
>> on the parent.
>
> In case of e.g. medical information systems, or in my case, maintenance
> information systems, quite a few people's lives could depend on the
> integrity of the data in the database. A correct data model is one
> necessary prerequisite for data integrity.
>
> Is that not enough of an incentive, to not practise homicide (murder?)
> by pure lazyness/ignorance/incompetence?
>
> Sincerely,
>
> Wolfgang
>
> P.S.: I would have expected people with such an obscene attitude ("why
> care for data correctness") on a list for Access or similar crapware,
> but not on a list for PostgreSQL.

Not every "0..*" relationship is necessarily wrong; nor is every "1..*"
relationship necessarily correct.  Since you have not provided any examples
of why you MUST have a 1..* relationship and why 0..* is doomed you haven't
really said anything of meaning.  I would argue that in many cases parents
can rightly exist without children and so 0..* is the natural model in life.
A menu with no options, An invoice without an line-items, a biological
parent without any offspring.

Your general tone for this entire thread, and this response to my posting in
particular, is quite offensive.  Given that there is a way to implement 1..*
when it is needed, and the fact that I would posit that many applications
either truly model 0..* or, in the few instances where 1..* would be more
correct, the risk taken on by using 0..* is negligible compared to the cost
of modelling said restriction, the status quo is understandable.

i would much like to hear situations where those risk factors are not
negligible for models that are truly 1..*.

I have attempted to explain, with limited knowledge of history, why the
feature that you wish for does not currently exist.  In short, the cost of
non-implementation has not yet outweighed the effort that would be required
for implementation.  Either explain (or contribute) to decrease the apparent
effort involved or explain (in detail) why the cost of non-implementation is
greater than what others suppose it is.  Railing against prevailing wisdom
and the decisions that people have made over the last 40 years - not to
mention insulting others - is a waste of effort that would be better spent
crafting a more detailed message.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Proper-solution-for-1-relationship-tp5753384p5754086.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

David Johnston <polobo@yahoo.com> wrote:

> i would much like to hear situations where those risk factors are
> not negligible for models that are truly 1..*.

For my part, the only cases I've run into where it was important to
ensure that children did in fact exist at commit time have been in
financial transactions; and in those cases ensuring that was
necessary *but not sufficient*.  A deferred trigger was necessary
anyway.  In the simplest case, you have a transaction header with
transaction type (assessment, receipt, adjustment, etc.),
transaction date, and other information about the transaction as a
whole, and a number of transaction detail records with the money
amounts.  We never wanted a header without detail, but we never
wanted a detail row with a zero money amount, *and the sum of the
detail rows for a transaction had to total zero*.  Positive numbers
represented debits and negative numbers credits in this
double-entry accounting system.  Of course, I'm vastly
oversimplifying, because there were many other things which needed
to tie out before the database transaction could be allowed to
commit.  Once committed, these were "written in ink" and could not
be updated again; errors had to be fixed by adding adjustment
transactions so that the entire history was always viewable.

I guess my point is that a declarative way to say that "at least
one child row is needed" would have been worth very little, because
of all the other checks which needed to be done in a deferred
trigger.  I suspect that there are cases where there would be value
in such a declarative constraint, but I've been working with a very
wide variety of organizations for decades without having come
across such a case yet.  Without some solid detail about real-world
use cases, nobody can do a good job of designing a feature.

I think it's safe to say that if anyone would demonstrate a clear
need for such a feature, and put forward resources for its
development, it would be added.  So far nobody has made such a
case; nor has anyone put any resources into developing such a
feature.

> Railing against prevailing wisdom and the decisions that people
> have made over the last 40 years - not to mention insulting
> others - is a waste of effort that would be better spent crafting
> a more detailed message.

Right.  Insulting people doesn't make the case, nor do much to
motivate anyone to work on such a feature.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Kevin Grittner-5 wrote
>  We never wanted a header without detail, but we never
> wanted a detail row with a zero money amount, *and the sum of the
> detail rows for a transaction had to total zero*. 

Even in this scenario I'd argue that the option to generate an entry without
any money detail would have value.  Imagine an accounting system that posts
service invoices.  Invoice numbers are supposed to be issued and consumed
sequentially and sometimes an invoice must be voided.  There is no
meaningful amount that you would want to post but having the accounting
system process that invoice anyway and generate an empty entry with "Voided
in Service" as a header comment would aid in auditing.

By allowing for zero-children you provide a richer model for the developer
to use.  It is kind of like "null".  The reason it exists (ignoring its
flaws here) is because complete knowledge or even applicability is not
possible - sometime the lack of data has meaning (or, IOW, reality is
manifested in the lack of the normal/usual).  Instead of artificially
creating because nothing is not allowed the model (and users of it) should
recognize that they need to understand and deal with absence just as much as
they need to deal with presence.  Lacking that either the model will be
unusable for those "absence" situations - thus limiting its ability to
reflect reality - or (worse) people will abuse the model to make their data
fit.

In the example above not tracking voids in accounting is not a great loss
but it could be useful functionality that the system could offer if the
model did not force 1..* cardinality.  If someone really wanted to track
voids in accounting they would have to program a journal entry to post
non-zero amounts to either memo accounts or a washing entry to the same
account.  While that is feasible (and possibly desirable now that I think of
it) it still seems messy at first glance.

Sure, you would need to design the query interfaces to deal with "empty"
documents but that too is a worthwhile exercise.  In reality I would allow
only certain kinds of documents to be posted "empty" (like invoices) and
then have a special interface so that users can account for and manage those
empty entries in a semantically meaningful way.

Alas, I digress.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Proper-solution-for-1-relationship-tp5753384p5754134.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.