U++ forum: Welcome to the forum

Re: Sql: *JoinRef [message #35411 is a reply to message #34979]

Fri, 10 February 2012 19:12

mirek
Messages: 14267
Registered: November 2005

Ultimate Member

After some experiences I have to say that this feature is a little bit dangerous, as it can eventually pick wrong 'connection' between tables. Worse, it can in some cases even break existing code by adding columns to tables. (Google "natural join sql" which is quite similar feature for explanation).

However, it still seems very useful and works just fine in most cases. To fix "wrong connection" problem, I have introduced heuristics that it first tries to pick foreign key with the same name as primary or foreign key key that has name table_pk (if FOO has primary key ID, then foreign key has to be FOO_ID) - this covers two most common naming conventions. This has fixed "picked wrong connection" problem for me.

As for other problem (instability in SQL issues), I guess that given this heuristics, it is something that can be avoided when designing and upgrading DB schema. I mean, if DB schema makes any sense, things should work.

Report message to a moderator