3 ms·

A foreign key is frequently just an integer. You can create a foreign key constraint that checks that the integer exists in a field in another table, but that i

A foreign key is frequently just an integer. You can create a foreign key constraint that checks that the integer exists in a field in another table, but that information isn't used by the database for anything other than checking that constraint (that I know of). In a select query, if you want to resolve that relationship in a join, you have to restate it as if the database knows nothing about it. To make the join use the foreign key information, you'd have to invent some new syntactic sugar, so that's a potential valid criticism.

dragonwriter 13y ago
> In a select query, if you want to resolve that relationship in a join, you have to restate it as if the database knows nothing about it. Okay, I see what you are saying. So, what you'd like is something like NATURAL JOIN that instead of using matching column names to infer join conditions it would use declared FK relations. That would be convenient -- but possible complicated by the possibility of multiple different FKs from one table to another (though the syntax could involve specifying the FK rather than the target table, which would solve that.) e.g., if you had CREATE TABLE emp ( emp_id INTEGER PRIMARY KEY, manager_id INTEGER FOREIGN KEY REFERENCES emp JOIN AS manager, hr_rep_id INTEGER FOREIGN KEY REFERENCES emp JOIN AS hr_rep, name VARCHAR(50), ... ); You could do something like: SELECT emp.name AS "Employee", manager.name AS "Manager" FROM emp NAMED JOIN emp.manager;
- mtdewcmu 13y ago
  That's pretty much it. If you want to do it with a minimum of new syntax, you could defer naming the join until the select, as you normally would: CREATE TABLE emp ( emp_id INTEGER PRIMARY KEY, manager_id INTEGER FOREIGN KEY REFERENCES emp, hr_rep_id INTEGER FOREIGN KEY REFERENCES emp, name VARCHAR(50), ... ); SELECT emp.name AS "Employee", manager.name AS "Manager" FROM emp NAMED JOIN emp.manager_id AS manager; I hadn't given any thought to the syntax, but that looks pretty nice and tidy. Edit: NAMED JOIN isn't really appropriate anymore with that change in syntax. Something like DECLARED JOIN might be better.
  - dragonwriter 13y ago
    Yeah, I originally had thought to do it more like that, but I figured that the big win from having language-level support for something like this was the ability to have clear semantic definitions in the declared relations and concise queries, so I thought naming in the Foreign Key was the best way. But I can see it either way.
    - mtdewcmu 13y ago
      I'd leave it to experts to design the best syntax, because they could foresee all the possible ramifications of different choices. I think we've clearly articulated the ends and some potential means.
    - mtdewcmu 13y ago
      You might have to give the join a name in the query in either case, because what if you want to go two levels deep, e.g. join in your boss's boss? Both can't be manager. All I'm interested in is making the queries more concise. Otherwise, I like the loose coupling between related entities in the relational model. I would have been hesitant to add join information to the table declaration if it wasn't already there. But since it's already there as a constraint, you might as well get all the mileage you can out of it.

dragonwriter 13y ago

> In a select query, if you want to resolve that relationship in a join, you have to restate it as if the database knows nothing about it. Okay, I see what you are saying. So, what you'd like is something like NATURAL JOIN that instead of using matching column names to infer join conditions it would use declared FK relations. That would be convenient -- but possible complicated by the possibility of multiple different FKs from one table to another (though the syntax could involve specifying the FK rather than the target table, which would solve that.) e.g., if you had CREATE TABLE emp ( emp_id INTEGER PRIMARY KEY, manager_id INTEGER FOREIGN KEY REFERENCES emp JOIN AS manager, hr_rep_id INTEGER FOREIGN KEY REFERENCES emp JOIN AS hr_rep, name VARCHAR(50), ... ); You could do something like: SELECT emp.name AS "Employee", manager.name AS "Manager" FROM emp NAMED JOIN emp.manager;

mtdewcmu 13y ago
That's pretty much it. If you want to do it with a minimum of new syntax, you could defer naming the join until the select, as you normally would: CREATE TABLE emp ( emp_id INTEGER PRIMARY KEY, manager_id INTEGER FOREIGN KEY REFERENCES emp, hr_rep_id INTEGER FOREIGN KEY REFERENCES emp, name VARCHAR(50), ... ); SELECT emp.name AS "Employee", manager.name AS "Manager" FROM emp NAMED JOIN emp.manager_id AS manager; I hadn't given any thought to the syntax, but that looks pretty nice and tidy. Edit: NAMED JOIN isn't really appropriate anymore with that change in syntax. Something like DECLARED JOIN might be better.
- dragonwriter 13y ago
  Yeah, I originally had thought to do it more like that, but I figured that the big win from having language-level support for something like this was the ability to have clear semantic definitions in the declared relations and concise queries, so I thought naming in the Foreign Key was the best way. But I can see it either way.
  - mtdewcmu 13y ago
    I'd leave it to experts to design the best syntax, because they could foresee all the possible ramifications of different choices. I think we've clearly articulated the ends and some potential means.
  - mtdewcmu 13y ago
    You might have to give the join a name in the query in either case, because what if you want to go two levels deep, e.g. join in your boss's boss? Both can't be manager. All I'm interested in is making the queries more concise. Otherwise, I like the loose coupling between related entities in the relational model. I would have been hesitant to add join information to the table declaration if it wasn't already there. But since it's already there as a constraint, you might as well get all the mileage you can out of it.