You are correct, however, if there were a unique constraint placed on tracks.name, then a given track could only appear once (in the first case), or in multiple places (in the second case).
> -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Ken Wagner > Sent: Monday, 9 January, 2017 23:46 > To: SQLite mailing list > Subject: Re: [sqlite] Foreign key error... > > Keith, > > "this does not allow the same track on multiple albums" with the same > trackno, but a different trackno seems to work. Thus results cannot be > guaranteed valid? > > Ken > > > On 01/08/2017 06:57 AM, Keith Medcalf 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.TheCommonColumnNameBetweenTheFirstAndTheSecondTabl > >> e = > >> > TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTab > >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
