>>>> 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.
>
> OK, let's assume that this knowledge can be used in a formal way
> (i.e. let's assume we didn't forget some weird corner-case
> involving some complex constraint setups).
I think it's indeed formally sound.
If the referred-to record is uniquely identified, it's a to-one
because only a single record can match (if any). If it's not
uniquely identified, it's a to-many because potentially multiple
records can match.
Establishing whether a given combination of fields is unique
can be a challenge.
One, some constraints other than unique keys can establish
uniqueness. I don't think that's an issue since people don't
expect query optimizers to recognize these kinds of uniqueness,
and hence construct their databases so that all uniqueness
constraints are expressed via unique keys.
Two, views built from joins typically don't provide information
on relevant unique keys. There may be ways around that, but it's
going to not work in all databases or for all kinds of views; on
the other hand, to establish primary key information, Jooq needs
to do something in that area anyway.
> According to you, should this change the way jOOQ deals with
> foreign key relationships? If yes, how?
I think it mostly affects code generation. If you generate a
reference to another table, you need to know whether it should
be a List or not.
Code that fills query results into Pojo structures will need to
know whether it's a List or not, too, but whether that kind of
code is affected depends on whether it's taking its uniqueness
information from the Pojo's metadata or from the RDBMS metadata.
>>> 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.
>
> How about
>
> CREATE TABLE x (
> val INT,
> CONSTRAINT x_to_y FOREIGN KEY(x) REFERENCES y(val)
> );
> INSERT INTO x VALUES (1), (1);
>
> Now, jOOQ's Record.equal() method would return true,
> if comparing the two records in table x.
That's why I'd use Java's equality, i.e. object identity, for
a Set.
> I would expect Y.getX{List|Set} to return two records, right?
Yep. SELECT DISTINCT should give different results than SELECT
in such cases, it would be highly surprising if it didn't.
> Nonetheless, most databases and JDBC allow for row access by
> index in tables and cursors, which is a strong indicator for
> using lists to me.
Yes, from the database side, it's lists.
I'm seeing it from the pojo side.
The read-only case on the pojo side is relatively uninteresting;
for what it's worth, one could declare a to-many reference as
Iterable and it would work well enough.
Things get interesting when the Java code adds records.
For a Set, multiply add()in the same record is okay, for a List,
it isn't, you need to check whether the record was in the List
before (and that check could be inefficient, depending on what
implementation is behind that List interface).
> And then, again, the way I see it, ROW(1) and ROW(1) are two
> Records / rows for which jOOQ should return r1.equals(r2) == true.
Hmm... I see the point.
However, in that case, object equality isn't the right comparator
for the Set, it should use object identity.
This probably means using a HashSet and a Comparator that uses ==.
Note that we should have r1 == r2 iff they refer to the same record
in the database.
To establish whether two records refer to the same database record,
Jooq would need to know all fields of any unique key. (This get
somewhat complicated if we're talking about a row from a query
result with aggregation and/or joins, but the concept does work
out well enough to be actually useful; we're in the tracks of the
"updatable view" concept here.)
