... join ... using (column) has nothing whatever to do with foreign keys.
"FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is sweetening and full of calories, but has no nutritional value) for the expression "FROM a, b WHERE a.c = b.c" This is so, for example, if you use really long stupid names it saves considerable space and typing: SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined USING (TheCommonColumnNameBetweenTheFirstAndTheSecondTable); -vs- SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTable = TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTable; > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Ken Wagner > Sent: Sunday, 8 January, 2017 04:04 > To: SQLite mailing list > Subject: Re: [sqlite] Foreign key error... > > Thanks, Kees, > > The message is helpful as a warning. > > select artistname, trackname from artist inner join track on > trackartist = artistid; works just fine. > > But isn't the efficiency of 'using (artistid)' more desirable? > > Is the use of a 'trackerartist' as the foreign key used because it is > more informative? > > I.e., wherever it is seen it shows the track-artist link? But is more > demanding when coding: > > 'on trackerartist = artistid' vs 'using (artistid)' > > Best or preferred SQLite3 practice for using which foreign reference > style 'trackartist' vs 'artistid'? > > Thanks, > Ken > > > On 01/08/2017 04:47 AM, Kees Nuyt wrote: > > On Sun, 8 Jan 2017 04:21:16 -0600, Ken Wagner > > <beauco...@gmail.com> wrote: > > > >> Hello SQLusers, > >> > >> The error below occurs even though the > >> > >> CREATE TABLE track( > >> > >> trackid INTEGER, > >> trackname TEXT, > >> trackartist INTEGER, > >> *FOREIGN KEY(trackartist) REFERENCES artist(artistid)* > >> ); > >> > >> statement at https://sqlite.org/foreignkeys.html was observed. > >> > >> It appears that 'trackerartist' should be named 'artistid'. > >> > >> SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37 > >> bbd85d235f7037c6a033a9690534391ffeacecc8 > >> > >> sqlite> select artistname, trackname from artist inner join track using > >> (trackartist); > >> Error:\ cannot join using column trackartist - column not present in > >> both tables > >> sqlite> .tables track > >> track > >> sqlite> .schema track > >> CREATE TABLE track( > >> trackid INTEGER, > >> trackname TEXT, > >> trackartist INTEGER, > >> FOREIGN KEY(trackartist) REFERENCES artist(artistid) > >> ); > >> sqlite> .schema artist > >> CREATE TABLE artist( > >> artistid INTEGER PRIMARY KEY, > >> artistname TEXT > >> > >> Am I missing something important here? > > The error message is quite informative: the artist table does > > not have a column trackartist. > > > > Try: > > select artistname, trackname from artist inner join track on > > trackartist = artistid; > > > > HTH > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
