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
