>> I usually stick with to-one and to-many in my terminology [...] > > to-one and to-many are higher-level concepts. SQL only knows > references / foreign keys. I.e. there is no such thing as a > one-to-one relationship in SQL.
It's a to-one relationship if the FK fully covers any unique key, to-many otherwise. Uniqueness is indeed a central concept in SQL. It is - used by any practitioner (not just for joins but about anywhere) - used by Jooq to decide whether it should be fetch[Entity] or fetch[Entity]List - even used in the standards, e.g. to determine what views could be updatable. >> Of course, you can combine a foreign key with a unique key and > a not null constraint on the referencing side to enforce an > (informal!) one-to-one semantics. It's the uniqueness that determines to-one vs. to-many. The not null constraints actually decides whether the to-one is optional or not. > Trying to automatically map referential constraints to higher-level > concepts is where Hibernate / JPA fails. Yes, but I found this particular detail to work well. It fails for views in Oracle because Oracle doesn't give you uniqueness information of views in metadata. However, this fails at the uniqueness (i.e. SQL) level, not at at higher level. > There is no easy way to automatically map what Sander is doing > with his multi-column referential constraints (from the > original post in this thread). Well, it isn't straightforward, but it's all there. > This is where the "object-oriented / relational impedance mismatch" > unveils fundamental problems in such mapping attempts. to-many and to-one actually map easily to a simple reference and a set, respectively. There's no fundamental mismatch here. In my book, the fundamental mismatches are elsewhere: - Java has no concept of a transaction; you can easily modify objects and forget to roll them back. The only clean workaround is to throw away the entire JVM after a transaction and repopulate the next transaction from the database. - Databases have no concept of subtype, or polymorphic table. You can work around this, but it remains awkward and can miss opportunities for constraint checking. - Many (most?) databases can't do enums as a primitive type. - Database types (table structures) and Java types (classes) cover overlapping semantics, creating a lot of implicit redundancy. >> Maybe say Set instead of List. Sets can't contain duplicates, >> which is a defining property here. > > In relational theory, Set might be a better choice. But there > are two drawbacks of choosing Set over List when fetching "child" > records through foreign key navigation: > > 1) It is possible to have two identical "child" records in SQL. > A Set would erroneously remove the duplicate. Only if the is_equal function does not fully cover any unique key of the "child" table. Solution: Use Java's is_equal. > 2) SQL allows for ordering records (a non-relational SQL > feature). > This semantic would be lost if Set was chosen. Java Sets do have an ordering, imposed by the iterator. It can be unstable, so whatever type of Set is used, it should implement SortedSet, reproducing the order in which the records were retrieved. > [Collision] can happen between ARTICLE and ARTICLE_LIST too, of > course. Which leads me back to wanting to remove these generated > methods in jOOQ 3.0 I can feel the pain, but having to define your own generator strategies would be more painful for 99% of use cases. I don't know enough details of Jooq's code generation to make meaningful proposals, unfortunately.
