Hacker News Reader

Comment by magicalhippo | Hacker News Reader

I've been longing for our db to add support for something ala

    select j.job_name, c.comp_name 
    from companies c
    join jobs j on foreign key

Could take optional name in case there's more than one foreign key linking the two.

View on HN

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!

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.

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.

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.

Reply on news.ycombinator.com