3 ms·

I do feel like this is an obvious thing for SQL to allow and databases to support. Given that it's not part of the standard (and NATURAL JOIN is) I can only ass

by ccakes 3y ago

I do feel like this is an obvious thing for SQL to allow and databases to support. Given that it's not part of the standard (and NATURAL JOIN is) I can only assume that there's a compelling reason that I haven't thought about. If anyone out there knows why this isn't a thing, please chime in!

setr 3y ago
As soon as you have multiple relationships to a table relying on different foreign / composite keys, I’m pretty sure this completely breaks down. And the second you add an additional foreign key, you break all queries — so you’ve turned foreign key constraints from an internally managed constraint to a public interface whose addition/removal at any point is a breaking change. Natural joins however depend on already publicly exposed metadata (column names), and if that changed you’d break queries anyways.
- wruza 3y ago
  At least they could make a syntax for joining on a “foreign key references” clause from create statement. E.g. “from a join b by a.b_id[, a.b_compound_id_2]”. But that’s just a sugar for still too low-level SQL. I’d better have well named relations and use these names in code. This way, there would be no situation when you add/remove a constraint but forget to add/remove a condition. create table a (id, b_id); create table b (id); create relation atob from a to b on a.b_id = b.id; select from a left join b using atob; This could also define classes of relationships to check in runtime. E.g. I’ve never sent all purchases with right join in my life, but had enough exploding relationships where 1:1 was expected.
- magicalhippo 3y ago
  But 99.99% of my child tables have only a single foreign keys linking it to its parent and will not have more. And breaking queries is much better than silently doing the wrong thing like NATURAL JOIN. For the edge cases it could support taking the name of the foreign key as an optional parameter.

by ccakes 3y ago

setr 3y ago

As soon as you have multiple relationships to a table relying on different foreign / composite keys, I’m pretty sure this completely breaks down. And the second you add an additional foreign key, you break all queries — so you’ve turned foreign key constraints from an internally managed constraint to a public interface whose addition/removal at any point is a breaking change. Natural joins however depend on already publicly exposed metadata (column names), and if that changed you’d break queries anyways.

wruza 3y ago
At least they could make a syntax for joining on a “foreign key references” clause from create statement. E.g. “from a join b by a.b_id[, a.b_compound_id_2]”. But that’s just a sugar for still too low-level SQL. I’d better have well named relations and use these names in code. This way, there would be no situation when you add/remove a constraint but forget to add/remove a condition. create table a (id, b_id); create table b (id); create relation atob from a to b on a.b_id = b.id; select from a left join b using atob; This could also define classes of relationships to check in runtime. E.g. I’ve never sent all purchases with right join in my life, but had enough exploding relationships where 1:1 was expected.
magicalhippo 3y ago
But 99.99% of my child tables have only a single foreign keys linking it to its parent and will not have more. And breaking queries is much better than silently doing the wrong thing like NATURAL JOIN. For the edge cases it could support taking the name of the foreign key as an optional parameter.