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
