Обсуждение: *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.http://www.postgresql.org/docs/9.2/interactive/sql-createtrigger.html
http://www.postgresql.org/docs/9.2/interactive/sql-set-constraints.html
On Fri, Apr 26, 2013 at 4:59 AM, 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?
TIA,
Sincerely,
Wolfgang
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
--
Regards,
Richard Broersma Jr.
> 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 02/05/13 03:37, Josh Kupershmidt wrote:
Maybe we (see note 1) should implement this feature, presumably with a Government health warning on the likely performance impact? It might serve as a 'checklist' feature for marketing.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
Notes:
1) "Who me paleface (see note 2)" said Tonto after the Lone Ranger had said "we are in danger!" when faced with a lot of warlike indians.
2) The Lone Ranger character was actually based on a very successful black lawman!!!
Cheers,
Gavin
> > 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.
On Thu, May 2, 2013 at 8:26 AM, Wolfgang Keller <feliphil@gmx.net> wrote:
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.
This is an inherent issue of the SQL Language. Perhaps TutorialD engines will better satisfy your data modeling needs.
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.