as an alternative you can use the same ID - nessecitating prefiixing the primary id with the table name... but then natural join can work. (Although common words like 'name' should also be table-name prefixed).
Having a 'id' as the primary key disallows the using() shorthand also. If all column names that are meant to be joined are the same name, database tools can extrapolate relationships; although with defined foriegn keys they should be able to manage with mismatched naming. But I suspect that the example from the sqlite documentation that you're starting with uses different names to illustrate what is actually meant to be used in which positions for the foriegn key definition. On Sun, Jan 8, 2017 at 4:57 AM, Keith Medcalf <kmedc...@dessus.com> wrote: > > On Sunday, 8 January, 2017 05:05, Ken Wagner <beauco...@gmail.com> wrote: > > > Keith, > > > > Ahh, better to be specific and avoid simplistic assumptions. > > > > For foreign keys which is better: 'trackerartist' or 'artistid' in the > > track file? Does it matter? Is it personal preference? > > It is a matter of personal preference. Personally, I use the same name > for the same data, and do not add useless prefaces, and usually do not > preface the id with the table name. For example: > > create table artists ( > id integer primary key, > name text collate nocase unique > ); > create table albums ( > id integer primary key, > name text collate nocase unique, > artistid integer references artists > ); > create table tracks ( > id integer primary key, > seq integer, > name text collate nocase, > artistid integer references artists, > albumid integer references albums, > unique (albumid, seq) > ); > > select albums.name as albumname, > albumartists.name as albumartist, > tracks.seq as trackno, > tracks.name as trackname, > trackartists.name as trackartist > from albums, tracks, artists as albumartists, artists as trackartists > where tracks.artistid = trackartists.id > and tracks.albumid = albums.id > and albums.artistid = albumartists.id; > > Of course, this does not allow the same track on multiple albums. For > that you need another table to do the N:M mapping: > > create table artists ( > id integer primary key, > name text collate nocase unique > ); > create table albums ( > id integer primary key, > name text collate nocase unique, > artistid integer references artists > ); > create table tracks ( > id integer primary key, > name text collate nocase, > artistid integer references artists, > ); > create table albumtracks ( > id integer primary key, > albumid integer references albums, > trackid integer references tracks, > seq integer, > unique (albumid, seq), > unique (albumid, trackid), > unique (trackid, albumid) > ); > > select albums.name as albumname, > albumartists.name as akbumartist, > albumtracks.seq as trackno, > tracks.name as trackname, > trackartists.name as trackartist > from albums, albumtracks, tracks, artists as albumartists, artists as > trackartists > where tracks.artistid = trackartists.id > and albumtracks.albumid = albums.id > and albumtracks.trackid = tracks.id > and albums.artistid = albumartists.id; > > > On 01/08/2017 05:46 AM, Keith Medcalf wrote: > > > ... 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.TheCommonColumnNameBetweenTheF > irstAndTheSecondTabl > > e = > > TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheF > irstAndTheSecondTab > > le; > > > > > > > > >> -----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 > > > > _______________________________________________ > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
